find text and delete rows

[Guest]

I would like to be able to find the word "service" and then select the row it
is in and the row below it and then delete both rows.

 

[Dave Peterson]

Option Explicit
Sub testme()
Dim FoundCell As Range

With Worksheets("Sheet1")
Set FoundCell = .Cells.Find(What:="service", _
After:=.Cells(.Cells.Count), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
End With

If FoundCell Is Nothing Then
MsgBox "Not found"
Else
FoundCell.Resize(2, 1).EntireRow.Delete
End If

End Sub

I looked in all the cells (not a specific column and for Service in a cell by
itself.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Guest]

Thanks. That works great. I have to run the macro repeatedly to to delete
the two rows that I want to get rid of. Is there a way to run it with the
whole worksheet as the range. Or actually not the whole worksheet but say
A1:L27860 as the range(as an example).

 

[Dave Peterson]

How about all of A:L?

Option Explicit
Sub testme()
Dim FoundCell As Range

With Worksheets("Sheet1").Range("A:L")
Do
Set FoundCell = .Cells.Find(What:="service", _
After:=.Cells(.Cells.Count), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

If FoundCell Is Nothing Then
Exit Do
End If

FoundCell.Resize(2, 1).EntireRow.Delete
Loop
End With

End Sub