Insert Rows

  • Thread starter Thread starter Jeff
  • Start date Start date
J

Jeff

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,

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
 
What I would do here is Name a cell on the first row of each section. Then
you can get the Row property of this range, and use it as the Row Counter
for each section.
In the macro, ensure that the last step is to automatically rename that
area's Name to the new first row in case it changes.

Any of the gurus out there want to suggest code for this amended routine?

1. Check which region that data has been entered.
2. Find Row Counter using that region's anchor name, and the anchor of the
region below it (taking into account bottom section has no lower anchor)
3. Insert Row
4. Rename Name if necessary

Question: when you say "insert row when data entered" do you mean, "insert
row if data is entered on a row that was previously blank", or ""insert row
if data is entered on a row even if that row previously contained data"?
You may need a check to handle that.
 
Back
Top