Macro to remove all rows that contain the word "sale"

[tonywatsonhelp]

Hi Everyone,
I have a sheet called "Raw Data"
I'd like a macro that can find ever instance of the word "Sale" and delete the entire row.
now i need it to be "sale" anytime, so if there's salesman it deletes the row, the range is "A2 to DA" and last row

please help if you can thanks
Tony

 

[alansidman]

Option Explicit

Option Compare Text
Sub Foo()
    Application.ScreenUpdating = False
    Dim c As Range, rng As Range
    Dim lr As Long, r As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row   'This assumes Column has last row
    Set rng = Range("A2:DA" & lr)                'Change this to your actual range you wish to scan
    For Each c In rng
        If InStr(c, "sale") > 0 Then
            r = c.Row
            Range("A" & r).EntireRow.Delete
        End If
    Next c
    Application.ScreenUpdating = True
    MsgBox "completed"
End Sub

 

[tonywatsonhelp]

Thank you Alansidman, this is exactly what i wanted
Tony

 

[excelnovicesir]

Hi!

I'm wondering if there a VBA for this to remove entire word that have multiple values. For example, I would like to remove rows that contain the word "sale" and "bow"?

Thank you!

excelnovicesir

 

[iggydarsa]

Change this line:

If InStr(c, "sale") > 0 Then

to:

If InStr(c, "sale") > 0 AND InStr(c, "bow") > 0 Then