Looping Macro

[Lori from Minnesota]

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

 

[ryguy7272]

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---

 

[Rick Rothstein]

Look up FindNext Method in the VBA help files... its example shows you how
to structure your loop (you can use Cells in place of the Range statement
they use in the With statement if you want to search the entire sheet;
otherwise, just change the range reference in the example to the range you
need your code to work over).

 

[Jim Cone]

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 (e-mail address removed)>
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