Format new rows.

[Fellow]

Jacob Skaria, you have helped me with this table a few times (see below) and
i hope you can help me again.

After testing i realised that the table was able to generate a new row
perfectly after entering data into the B column. However, if i were to go
back to change the data previously entered, a new row will appear. I now have
two blank rows. Is there any way i can limit adding new rows with only new
entries and therefore change existing data without adding a new row?

Thanks again for all your help.


"Hi again

Try the below modified one to suit your requirement

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A1:B20")) Is Nothing Then
If Target <> "" Then
Application.EnableEvents = False
If Range("A" & Target.Row) <> "" Then
Rows(Target.Row + 1).Insert
Range("A" & Target.Row + 1) = Range("A" & Target.Row)
End If
Application.EnableEvents = True
End If
End If
End Sub

 

[Jacob Skaria]

Try the below

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("B1:B100")) Is Nothing Then
If Target <> "" Then
Application.EnableEvents = False
If Range("A" & Target.Row) <> "" Then
If Range("A" & Target.Row + 1) <> Range("A" & Target.Row) Then
Rows(Target.Row + 1).Insert
Range("A" & Target.Row + 1) = Range("A" & Target.Row)
End If
End If
Application.EnableEvents = True
End If
End If
End Sub



--
Jacob

Jacob Skaria, you have helped me with this table a few times (see below) and
i hope you can help me again.

After testing i realised that the table was able to generate a new row
perfectly after entering data into the B column. However, if i were to go
back to change the data previously entered, a new row will appear. I now have
two blank rows. Is there any way i can limit adding new rows with only new
entries and therefore change existing data without adding a new row?

Thanks again for all your help.


"Hi again

Try the below modified one to suit your requirement

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A1:B20")) Is Nothing Then
If Target <> "" Then
Application.EnableEvents = False
If Range("A" & Target.Row) <> "" Then
Rows(Target.Row + 1).Insert
Range("A" & Target.Row + 1) = Range("A" & Target.Row)
End If
Application.EnableEvents = True
End If
End If
End Sub

--
Jacob

My table looks like this:

A B C D E
1 Organisation Adress Contact Outcome
2 Employer
3 Industry
4 Schools
5 Referral
6 Employment
7 Groups
8 Training
9 Community
10 Media
11 Other

Jacob Skaria was able to help me generate a new row when data is entered
into each category [(eg when new data is added to B2 a new line will
automatically appear at 3 to allow room for new data for the employer
category). A new line would insert for each of the column A categories in the
same way] with the VBA solution:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A1:B20")) Is Nothing Then
If Target <> "" Then
Application.EnableEvents = False
If Range("A" & Target.Row + 1) <> "" Then Rows(Target.Row + 1).Insert
Application.EnableEvents = True
End If
End If
End Sub

This was most helpful.

However, I would also like to be able to keep entries with their categories
so they are not confused when i filter them (e.g. when new data is entered
into B2 and a new row appears I would like column A to also display the
category name in the new row at 3. for example A3 will now have the name
"employer" as well as A2. This would be replicated for each of the
categories.

Thankyou for your time and help with this, much appreciated.

 

[Fellow]

Yet again, you have solved my problem. Many Thanks Jacob.

Try the below

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("B1:B100")) Is Nothing Then
If Target <> "" Then
Application.EnableEvents = False
If Range("A" & Target.Row) <> "" Then
If Range("A" & Target.Row + 1) <> Range("A" & Target.Row) Then
Rows(Target.Row + 1).Insert
Range("A" & Target.Row + 1) = Range("A" & Target.Row)
End If
End If
Application.EnableEvents = True
End If
End If
End Sub



--
Jacob

Jacob Skaria, you have helped me with this table a few times (see below) and
i hope you can help me again.

After testing i realised that the table was able to generate a new row
perfectly after entering data into the B column. However, if i were to go
back to change the data previously entered, a new row will appear. I now have
two blank rows. Is there any way i can limit adding new rows with only new
entries and therefore change existing data without adding a new row?

Thanks again for all your help.


"Hi again

Try the below modified one to suit your requirement

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A1:B20")) Is Nothing Then
If Target <> "" Then
Application.EnableEvents = False
If Range("A" & Target.Row) <> "" Then
Rows(Target.Row + 1).Insert
Range("A" & Target.Row + 1) = Range("A" & Target.Row)
End If
Application.EnableEvents = True
End If
End If
End Sub

--
Jacob

My table looks like this:

A B C D E
1 Organisation Adress Contact Outcome
2 Employer
3 Industry
4 Schools
5 Referral
6 Employment
7 Groups
8 Training
9 Community
10 Media
11 Other

Jacob Skaria was able to help me generate a new row when data is entered
into each category [(eg when new data is added to B2 a new line will
automatically appear at 3 to allow room for new data for the employer
category). A new line would insert for each of the column A categories in the
same way] with the VBA solution:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A1:B20")) Is Nothing Then
If Target <> "" Then
Application.EnableEvents = False
If Range("A" & Target.Row + 1) <> "" Then Rows(Target.Row + 1).Insert
Application.EnableEvents = True
End If
End If
End Sub

This was most helpful.

However, I would also like to be able to keep entries with their categories
so they are not confused when i filter them (e.g. when new data is entered
into B2 and a new row appears I would like column A to also display the
category name in the new row at 3. for example A3 will now have the name
"employer" as well as A2. This would be replicated for each of the
categories.

Thankyou for your time and help with this, much appreciated.