Expert: David Barrett - 8/27/2008

Question
I would like to move to each row with the word "Total" in it and delete the row and repeat until every row with the word "Total" is deleted.

Answer
Example of this:

Sub DeleteTotal()
  Dim rngTotal As Range
  Set rngTotal = Cells.Find(What:="Total", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
      :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
      False, SearchFormat:=False)
  While Not rngTotal Is Nothing
     Rows(rngTotal.Row).Delete
     Set rngTotal = Cells.Find(What:="Total", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
         :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
         False, SearchFormat:=False)
  Wend
End Sub



The subroutine above will keep searching for the word "Total" (not case sensitive) and delete the row until no more occurrences of the word are found.  Note that it currently works on the active worksheet, but can easily be adapted to work on specific sheets as needed.