loop to find text and remove rows

[ruoh_htxi]

i am trying to write a macro to remove some page headers. i need to find each occurance of some text and then remove the row it is on and the next 8 rows. the macro below works but it finishes with an error and the line highlighted in yellow in Visual Basic is Loop While Not c Is Nothing And c.Address <> firstAddress

With ActiveSheet.UsedRange.Cells

Set c = .Find(What:="Micro Syste", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Select
ActiveCell.Rows("1:9").EntireRow.Select
Selection.Delete Shift:=xlUp

Set c = .FindNext()

Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

i don't fully understand the logic of the macro because i have created it by copying from examples even though i have tried looking things up in the VB help programme. my guess of what the code does are the comments in brown:

With ActiveSheet.UsedRange.Cells tells it to only look at cells with something in

Set c = .Find(What:="Micro Syste", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False) sets a variable called c so where c is found in the code it will look for "Micro Syste" text.
If Not c Is Nothing
firstAddress = c.Address this creates a variable called firstAddress with the cell reference of the text found by c?
Do
c.Select makes the cell found active
ActiveCell.Rows("1:9").EntireRow.Select
Selection.Delete Shift:=xlUp selects 9 rows and deletes them

Set c = .FindNext() resets the variable c to the cell reference of the next occurance of the text

Loop While Not c Is Nothing And c.Address <> firstAddress keeping looping is all i understand here
End If
End With

[ruoh_htxi]

the macro removes all the page headers so i should just be satisfied, i mean what does it matter that much if it finishes with an error after it has done the job. but i want it to be tidy . i'm thinking it could be because the rows are being deleted, maybe this means the cell reference used in c.Address <> firstAddress doesn't exist anymore because it has been deleted and this gives an error? to support my theory i removed ActiveCell.Rows("1:9").EntireRow.Select and Selection.Delete Shift:=xlUp and replaced it with c.EntireRow.Font.Bold = True i.e. making it bold instead of deleting it and low and behold the error didn't happen which seems to support my theory. this is just an aside but i also had to change Set c = .FindNext() to Set c = .FindNext(c) to get the bold to work more than once whereas the macro to remove rows doesn't like the c being there. anyway assuming the error is caused by the rows being deleted i have now got to think what to do about that...or you could help me

[ruoh_htxi]

sorted


With ActiveSheet.UsedRange.Cells

Set c = .Find("Micro Syste", LookIn:=xlValues)
firstAddress = c.Address
c.Select
ActiveCell.Rows("2").Select

Set c = .Find(What:="Micro Syste", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False)
If Not c Is Nothing Then

Do
c.Select
ActiveCell.Rows("1:9").EntireRow.Select
Selection.Delete Shift:=xlUp

Set c = .Find(What:="Micro Syste", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False)

Loop While Not c Is Nothing And c.Address <> firstAddress
End If
Cells.Find(What:="Micro Syste", LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Rows("1:9").EntireRow.Select
Selection.Delete Shift:=xlUp

End With

think this is how it works: it finds the first occurance and assigns it to firstAddress but doesn't delete it. then it goes to the next line and then looks further down for the next occurance , deletes it and loops round deleting all other occurances before coming back to the first occurance that wasn't deleted and which point it comes out of the loop and then it finds the first occurance again, which is the only one left, and deletes it.