automatic search and delete

  • Thread starter Thread starter David Gerrish
  • Start date Start date
D

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
 
Hi David
you may try the following (an adaption of a procedure Chip Pearson
posted yesterday)

Sub delete_rows()

Dim RowNdx As Long
Dim LastRow As Long
Dim LastCol As Integer
Dim ColIndex As Integer

LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).row
LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
For RowNdx = LastRow To 1 Step -1
For ColIndex = 1 To LastCol
If Cells(RowNdx, ColIndex).Value = "the word" Then
Rows(RowNdx).Delete
Exit For
End If
Next ColIndex
Next RowNdx

End Sub


This procedures uses column A and row 1 to determine the worksheet (max
column / max row) extent to reduce processing time
 
David
This macro looks for "The Word" only if "The Word" is alone in the cell.
That is, it is not a part of the text in the cell. HTH Otto
Sub DeleteRows()
Dim c As Long
Application.ScreenUpdating = False
For c = 1 To ActiveSheet.UsedRange.Rows.Count
On Error GoTo NoMore
ActiveSheet.UsedRange.Find(What:="The Word", _
LookAt:=xlWhole).EntireRow.Delete
On Error GoTo 0
Next
NoMore:
On Error GoTo 0
MsgBox "Done."
Application.ScreenUpdating = True
End Sub
 
Back
Top