Delete entire row with a condition

[YasserKhalil]

Hi everybody
I want a code that could delete entire row if the cell value doesn't contain specific text say "man"

 

[YasserKhalil]

Note ::
There are empty rows in the range
May be the codes above works well but the problem in empty rows...

 

[YasserKhalil]

Note ::
There are empty rows in the range
May be the codes above works well but the problem in empty rows...

I deleted the empty rows and the code doesn't work well


Please help me

 

[YasserKhalil]

Please help me

 

[YasserKhalil]

Is it difficult or what?

 

[YasserKhalil]

UP

 

[taurean]

If your target is to retain the rows where word "man" (precisely!) is found then try the following code on a backup sheet:

Public Sub DelRow()
Dim r As Range, rFind As Range
Application.ScreenUpdating = False
Set rFind = Range("A1:A100").Find(What:="man", After:=Range("A1"), LookIn:=xlFormulas, Lookat:=xlWhole)
If Not rFind Is Nothing Then
    Set r = Range("A1:A100").ColumnDifferences(Comparison:=rFind)
    If Not r Is Nothing Then r.EntireRow.Delete: Exit Sub
Else
MsgBox "Search Word : Man is not found!"
End If
Application.ScreenUpdating = True
End Sub

 

[YasserKhalil]

thanks for the code
but the text "man" is part of string not in the whole of the cell

 

[taurean]

just "man"

Then why didn't you answer clearly to Trebor76's query at #4?

 

[YasserKhalil]

Then why didn't you answer clearly to Trebor76's query at #4?

I'm sorry
I didn't understand what he means.......
generally the text "man" is embedded in the cell value

 

[taurean]

Then try this, hard coded for 100 rows

Public Sub DelRow2()
Application.ScreenUpdating = False
For i = 100 To 1 Step -1
    If InStr(Range("A" & i).Value, "man") = 0 Then Range("A" & i).EntireRow.Delete
Next i
Application.ScreenUpdating = True
End Sub