keyword search and delete row

M

mikeyVo

I have a database of thousands of contacts. I would like to delete all
contacts with a certain word. is there a way to search for a word,
then if excel finds the word, delete the row? I have been doing it
manually and it is getting too time consuming
Thanks for your help!
 
B

bernd.vd.berg

I've use this macro to delete rows that didn't contain a 0 in column V.
It is the inverse of what you are loooking for, but I think you can use
this macro. You have to add an extra column (take column V in order to
copy paste this macro) where you put a 0 if the special word is in the
row, use an if then else funtion.

---
Sub Reset()
ActiveSheet.Unprotect
Dim NullRange As Range
Set NullRange = Nothing
Application.ScreenUpdating = False
For i = 1 To ActiveSheet.UsedRange.Rows.Count
If Range("V" & i).Value = "" Then 'If cell in column V is empty,
then add to NullRange.
If NullRange Is Nothing Then 'If NullRange were non-empty to
start, this If could be removed leaving the Else action as the only one
to perform.
Set NullRange = (Cells(i, 1))
Else
Set NullRange = Union(NullRange, Cells(i, 1))
End If
End If
Next

If Not (NullRange Is Nothing) Then
NullRange.EntireRow.Delete 'Delete the rows which satisfy the
condition.
End If
Application.ScreenUpdating = True
Call subreset
ActiveSheet.Protect
End Sub
 
Z

Zone

Here's an alternate way to do it. Might be a bit crude, but it's
simple and effective. Copy and put in a standard module. Change Const
WordToFind to whatever it is you want to find. Change Const Col to
whatever column you want to look in. Sub presumes sheet is not
protected. Also note that it will delete if "draw", "Draw", or "DRAW"
is matched. James

Sub SearchAndDestroy()
Const WordToFind = "draw"
Const Col = "a"
Dim k As Long
For k = Cells(65535, Col).End(xlUp).Row To 1 Step -1
If InStr(1, Cells(k, Col), WordToFind, vbTextCompare) > 0 _
Then Rows(k).EntireRow.Delete
Next k
End Sub
 
Top