N
nj
Hi,
I am trying to insert 2 rows above and below the rows where column B
ends with the word "Total". But when I insert the first set of rows,
that first group footer is shifted down, so the macro finds it again,
and inserts 2 more rows around it!
I didn't think I could redefine the range inside the loop, but it came
closer to working than I expected, but it's still not working. If
that's the right idea, I'm not quite there. And it's entirely possible
I'm just going at it the wrong way.
I'm going to get protein, and see if moving around gains me some
clarity but, in the meanwhile, if anyone cares to toss it out off their
top of their head, that would be lovely too!
LastRow = Range("B1").SpecialCells(xlLastCell).Row
Range(Range("B11"), Cells(LastRow, 2)).Select
For Each Cell In Selection
If InStr(Cell.Value, "Total") Then
Cell.EntireRow.Insert
Cell.EntireRow.Insert
Cell.Offset(1, 0).EntireRow.Insert
Cell.Offset(1, 0).EntireRow.Insert
NewStart = Cell.Row
'Reset Selection?
Range(Cells(NewStart + 1, 2), Cells(LastRow, 2)).Select
End If
Next Cell
Well, looking at it again, before sending, it occurs to me that I
probably need to end the loop on find, and start over below. So, an
outer/inner loop situation? Seems like overkill, but perhaps....
Thanks!
NJ
I am trying to insert 2 rows above and below the rows where column B
ends with the word "Total". But when I insert the first set of rows,
that first group footer is shifted down, so the macro finds it again,
and inserts 2 more rows around it!
I didn't think I could redefine the range inside the loop, but it came
closer to working than I expected, but it's still not working. If
that's the right idea, I'm not quite there. And it's entirely possible
I'm just going at it the wrong way.
I'm going to get protein, and see if moving around gains me some
clarity but, in the meanwhile, if anyone cares to toss it out off their
top of their head, that would be lovely too!
LastRow = Range("B1").SpecialCells(xlLastCell).Row
Range(Range("B11"), Cells(LastRow, 2)).Select
For Each Cell In Selection
If InStr(Cell.Value, "Total") Then
Cell.EntireRow.Insert
Cell.EntireRow.Insert
Cell.Offset(1, 0).EntireRow.Insert
Cell.Offset(1, 0).EntireRow.Insert
NewStart = Cell.Row
'Reset Selection?
Range(Cells(NewStart + 1, 2), Cells(LastRow, 2)).Select
End If
Next Cell
Well, looking at it again, before sending, it occurs to me that I
probably need to end the loop on find, and start over below. So, an
outer/inner loop situation? Seems like overkill, but perhaps....
Thanks!
NJ