I have a spreadsheet that is divided into three sub
sections: 1)Rows(2:5), 2) Rows(8:11), and 3)Rows(14:17).
I would like to create the ability for row insertion after
data is entered within any of the rows in the
subsections. I have the following code:

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


The problem with this code is the fact that if I press
ener in a row in section 1, then go to section 3 and press
enter, I am in trouble. Is there a fix where I can reset
the counter or do I need to take a different approach?

Jeff

the code was designed to meet the requirement of inserting a line when you
enter data on row 3 and then on lines in that "section". If you think it
through, once you've edited row 3 and hence inserted a row for the next
input, the row you have to test for is 4; when you edit line 4 you have to
test for line 5 and so on. Naturally that increments the row number of
every subsequent row. So you need to keep one counter which reflects the
rows you have inserted.

I'm not sure now which rows you want to check, but assuming it is the last
in each section you need something like:

If Target.Row = 5 + RowCounter Or
If Target.Row = 11 + RowCounter Or
If Target.Row = 17 + RowCounter Then

However, this wouldn't work if you amended data in section 2 and then went
back to section 1 because this would have already incremented the count.
Maybe better would be separate counts for each section:

Private Sub Worksheet_Change(ByVal Target As Range)
Static S1Ctr As Long
Static S2Ctr As Long
Static S3Ctr As Long

If Target.Row = 5 + S1Ctr Then
Rows(Target.Row + 1).EntireRow.Insert
S1Ctr = S1Ctr + 1
End If

If Target.Row = 11 + S1Ctr + S2Ctr Then
Rows(Target.Row + 1).EntireRow.Insert
S2Ctr = S2Ctr + 1
End If

If Target.Row = 17 + S1Ctr + S2Ctr + S3Ctr Then
Rows(Target.Row + 1).EntireRow.Insert
S3Ctr = S3Ctr + 1
End If

End Sub

Bit unwieldy but I think it works. Beware though that deleting rows could
screw this completely

It's probably best to stay in the same thread so that everyone knows how
you've got where you are.

Regards

Trevor


"Jeff" wrote in message
...
I have a spreadsheet that is divided into three sub
sections: 1)Rows(2:5), 2) Rows(8:11), and 3)Rows(14:17).
I would like to create the ability for row insertion after
data is entered within any of the rows in the
subsections. I have the following code:

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


The problem with this code is the fact that if I press
ener in a row in section 1, then go to section 3 and press
enter, I am in trouble. Is there a fix where I can reset
the counter or do I need to take a different approach?

Jeff,

I'm not sure what you are trying to do but if rows 6-7 and 12-13 are empty, a different approach could be to look into CurrentRegion (see Help). That way you can find out the row number of the Target in it's section and take appropriate action. Also, then you don't need the RowCounter variable.

HTH
Anders Silvén

"Jeff" skrev i meddelandet ...
I have a spreadsheet that is divided into three sub
sections: 1)Rows(2:5), 2) Rows(8:11), and 3)Rows(14:17).
I would like to create the ability for row insertion after
data is entered within any of the rows in the
subsections. I have the following code:

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


The problem with this code is the fact that if I press
ener in a row in section 1, then go to section 3 and press
enter, I am in trouble. Is there a fix where I can reset
the counter or do I need to take a different approach?