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