Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping Macro
Hi,
I have the following macro that runs perfectly, but the number of times it needs to loop is not always going to be 21. Could someone please tell me how to modify it so that it continues to loop until it doesn't find any more instances of [MID BS SA]? What I'm doing is deleted all the extra headers in a report, but I want the initial, first page header to remain. Here's my macro: Range("A1").Select Cells.Find(What:="MID BS SA", After:=ActiveCell, LookIn:= _ xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _ xlNext, MatchCase:=True, SearchFormat:=False).Activate For Counter = 1 To 21 ActiveCell.Select Cells.Find(What:="MID BS SA", After:=ActiveCell, LookIn:= _ xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _ xlNext, MatchCase:=True, SearchFormat:=False).Activate ActiveCell.Offset(-4, 0).Rows("1:10").EntireRow.Select Selection.Delete Shift:=xlUp Next Counter Range("A1").Select End Sub Any help you can give will be greatly appreciated. Thanks so much, Lori |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping Macro
Are the values of interest in rows?
Maybe something like this: Dim lLastRow As Long For i = 0 To lLastRow - 1 .... Next i Or this: Dim myRow As Long lastcell = Cells(Rows.Count, "A").End(xlUp).Row myRow = 1 Do Until myRow = lastcell For i = 1 To Cells(myRow, 1) .... Next lastcell = Cells(Rows.Count, "A").End(xlUp).Row myRow = myRow + 1 Loop HTH, Ryan--- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping Macro
Use a "Do" loop instead. However, you have to give a Do Loop
specific instructions on when to stop looping. This loop is a little different than most because deleting a row kills the range object. You have determine whether to stop the loop before deleting the row. '-- Sub OnlyOneHeader() Dim firstFound As Range Dim othersFound As Range Set firstFound = ActiveSheet.Cells.Find(What:="MID BS SA", _ After:=ActiveSheet.Range("A1"), _ LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False) If firstFound Is Nothing Then MsgBox "Nothing found " Exit Sub End If Do Set othersFound = ActiveSheet.Cells.Find(What:="MID BS SA", _ After:=firstFound, LookIn:=xlFormulas, LookAt:=xlWhole, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=True, SearchFormat:=False) If othersFound Is Nothing Then Exit Do ElseIf othersFound.Address = firstFound.Address Then Exit Do End If othersFound.EntireRow.Delete shift:=xlUp Loop Range("A1").Select End Sub -- Jim Cone Portland, Oregon USA "Lori from Minnesota" <Lori from wrote in message Hi, I have the following macro that runs perfectly, but the number of times it needs to loop is not always going to be 21. Could someone please tell me how to modify it so that it continues to loop until it doesn't find any more instances of [MID BS SA]? What I'm doing is deleted all the extra headers in a report, but I want the initial, first page header to remain. Here's my macro: Range("A1").Select Cells.Find(What:="MID BS SA", After:=ActiveCell, LookIn:= _ xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _ xlNext, MatchCase:=True, SearchFormat:=False).Activate For Counter = 1 To 21 ActiveCell.Select Cells.Find(What:="MID BS SA", After:=ActiveCell, LookIn:= _ xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _ xlNext, MatchCase:=True, SearchFormat:=False).Activate ActiveCell.Offset(-4, 0).Rows("1:10").EntireRow.Select Selection.Delete Shift:=xlUp Next Counter Range("A1").Select End Sub Any help you can give will be greatly appreciated. Thanks so much, Lori |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looping Macro | Excel Discussion (Misc queries) | |||
Looping macro | Excel Programming | |||
Looping a Macro | Excel Programming | |||
Looping macro | Excel Worksheet Functions | |||
Looping a macro | Excel Discussion (Misc queries) |