Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Inserting rows conditionally, how to work with shifting range
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Inserting rows conditionally, how to work with shifting range
I found an elegant solution in another article. Moving upward thru the
range. For i = LastRow To 2 Step -1 If InStr(Cells(i, 2), "Total") Then Rows(i).Insert End If Next nj wrote: 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! 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Inserting rows conditionally, how to work with shifting range
Sub aBC()
Dim lastrow As Long Dim i As Long Dim cell As Range lastrow = Range("B1").SpecialCells(xlLastCell).Row i = lastrow Do While i 11 Set cell = Cells(i, 2) If InStr(cell.Value, "Total") Then cell.EntireRow.Insert cell.EntireRow.Insert cell.Offset(1, 0).EntireRow.Insert cell.Offset(1, 0).EntireRow.Insert i = i - 3 Else i = i - 1 End If Loop End Sub seemed to work. -- Regards, Tom Ogilvy "nj" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
inserting a hyperlink into a conditionally formatted cell, Excel 2 | Excel Discussion (Misc queries) | |||
Office 2007 Excel - Inserting Copied or cut rows from another work | Excel Discussion (Misc queries) | |||
Inserting images into excel conditionally | New Users to Excel | |||
Auto Update upon shifting, inserting or deleting | Excel Worksheet Functions | |||
Conditionally inserting blank rows using macro | Excel Programming |