Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code- Row Insertion
I have broken up a spreadsheet into several sections. In
each section, I would like to enter code that would allow the user, once to the last row of each section, to automatically insert another row(s). I have attempted to write the code (see below) for this, but there is one problem. Once one row is entered, the new row that is created does not allow for another row to be created, i.e. if the code is referenced to row 3 and a new row is inserted (row 4), once the user hits enter in row 4, a new row cannot be created. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row = 3 Then Rows(Target.Row + 1).EntireRow.Insert End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code- Row Insertion
Jeff
maybe this would do it: If Target.Row = 3 Then I don't know how this would affect other "sections" Regards Trevor "Jeff" wrote in message ... I have broken up a spreadsheet into several sections. In each section, I would like to enter code that would allow the user, once to the last row of each section, to automatically insert another row(s). I have attempted to write the code (see below) for this, but there is one problem. Once one row is entered, the new row that is created does not allow for another row to be created, i.e. if the code is referenced to row 3 and a new row is inserted (row 4), once the user hits enter in row 4, a new row cannot be created. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row = 3 Then Rows(Target.Row + 1).EntireRow.Insert End If End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code- Row Insertion
Trevor,
Thank you for your response, but I believe this would allow row insertion for every row after 3, but would not work for multiple sections (i.e. i do not want certain rows after 3 to allow for insertion). Anyone else have a solution. -----Original Message----- Jeff maybe this would do it: If Target.Row = 3 Then I don't know how this would affect other "sections" Regards Trevor "Jeff" wrote in message ... I have broken up a spreadsheet into several sections. In each section, I would like to enter code that would allow the user, once to the last row of each section, to automatically insert another row(s). I have attempted to write the code (see below) for this, but there is one problem. Once one row is entered, the new row that is created does not allow for another row to be created, i.e. if the code is referenced to row 3 and a new row is inserted (row 4), once the user hits enter in row 4, a new row cannot be created. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row = 3 Then Rows(Target.Row + 1).EntireRow.Insert End If End Sub . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code- Row Insertion
OK, give this a try:
Private Sub Worksheet_Change(ByVal Target As Range) Static RowCounter As Long If Target.Row = 3 + RowCounter Then Rows(Target.Row + 1).EntireRow.Insert RowCounter = RowCounter + 1 End If End Sub Regards Trevor wrote in message ... Trevor, Thank you for your response, but I believe this would allow row insertion for every row after 3, but would not work for multiple sections (i.e. i do not want certain rows after 3 to allow for insertion). Anyone else have a solution. -----Original Message----- Jeff maybe this would do it: If Target.Row = 3 Then I don't know how this would affect other "sections" Regards Trevor "Jeff" wrote in message ... I have broken up a spreadsheet into several sections. In each section, I would like to enter code that would allow the user, once to the last row of each section, to automatically insert another row(s). I have attempted to write the code (see below) for this, but there is one problem. Once one row is entered, the new row that is created does not allow for another row to be created, i.e. if the code is referenced to row 3 and a new row is inserted (row 4), once the user hits enter in row 4, a new row cannot be created. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row = 3 Then Rows(Target.Row + 1).EntireRow.Insert End If End Sub . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Row Insertion | Excel Discussion (Misc queries) | |||
Data insertion | Excel Worksheet Functions | |||
Automatic row insertion | Excel Discussion (Misc queries) | |||
Rows Insertion | Excel Worksheet Functions | |||
Rows Insertion | Excel Worksheet Functions |