automatic search and delete

[David Gerrish]

Hi there

Does anyone know how to search for text string and automatically delete the
row that contains that text string.

The problem is i want to search a whole worksheet for a certain word and
when it finds that word, I would like that whole row deleted and then
continue until it has deleted all the rows that contain 'the' word i was
looking for.

Going mad

Thanks in advance

David

 

[BrianB]

This should do it. Copy/paste into a code module:-

'--------------------------------------------------------------------
Sub DELETE_ROWS()
Dim MyFindValue
Dim FoundCell As Object
Dim FoundRow As Long
'----------------------
MyFindValue = InputBox("Please enter your magic word.", " DELET
ROWS")
If MyFindValue = "" Then End
'----------------------
'- mark rows to delete
With ActiveSheet.Cells
Set FoundCell = .Find(MyFindValue, LookIn:=xlValues)
If Not FoundCell Is Nothing Then
FirstAddress = FoundCell.Address
Do
FoundRow = FoundCell.Row
ActiveSheet.Cells(FoundRow, 1).Value = "delete"
Set FoundCell = .FindNext(FoundCell)

Loop While Not FoundCell Is Nothing And FoundCell.Addres
<> FirstAddress
End If
End With
'------------------------
'- delete rows
R = 1
While ActiveSheet.Cells(R, 1).Value <> ""
If ActiveSheet.Cells(R, 1).Value = "delete" Then
ActiveSheet.Rows(R).EntireRow.Delete
Else
R = R + 1
End If
Wend
MsgBox ("Done")
End Sub
'-----------------------------------------------------------------------