VBA Code- Row Insertion

[Jeff]

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

 

[Trevor Shuttleworth]

Jeff

maybe this would do it:

If Target.Row >= 3 Then

I don't know how this would affect other "sections"

Regards

Trevor

 

[Guest]

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.

 

[Trevor Shuttleworth]

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