Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
format new rows
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:G20")) 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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
format new rows
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:G20")) 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 "Fellow" wrote: 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:G20")) 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
format new rows
Once again, thankyou Jacob. You are right on the money.
"Jacob Skaria" wrote: 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:G20")) 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 "Fellow" wrote: 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:G20")) 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Enabling option „Format rows“ to hide/unhide rows using VBA-code? | Excel Discussion (Misc queries) | |||
Freeze rows in htm format | Excel Discussion (Misc queries) | |||
Format rows | Excel Discussion (Misc queries) | |||
conditional format rows | Excel Discussion (Misc queries) | |||
can't format rows to autofit | Excel Discussion (Misc queries) |