Delete entire row for specific words

Hello everybody

I need a macro that enables me to select a range using inputbox ...
As for selection I need to use (Intersect) method to focus on column D in my selection ...
I need to put about seven words in an array , e.g (Cat1,Cat2,Cat3,Cat4,Cat5,Cat6,Cat7) ...

If any cell in column D in that selection only not the whole column :: if any cell has any string of the array the macro delete the entire row

Hope it is clear

Hello YasserKhalil,

See if this works for you :

Sub DeleteRows()
    Dim rg As Range
    Dim ar As Variant
    Dim i As Long
    
    Application.ScreenUpdating = False

    Set rg = Application.InputBox("Select range", , , , , , , 8)

    Set rg = Intersect(rg, Columns("D"))
    If rg Is Nothing Then MsgBox "Invalid selection": Exit Sub

    ar = InputBox("Enter words separated by comma")
    ar = Split(ar, ",")

    'Search words, delete rows
    With rg.Offset(-1, 0).Resize(rg.Rows.Count + 1, 1)
        For i = LBound(ar) To UBound(ar)
            .AutoFilter 1, ar(i)
            On Error Resume Next
            rg.SpecialCells(xlCellTypeVisible).EntireRow.Delete
            On Error GoTo 0
            .AutoFilter
        Next i
    End With

    Application.ScreenUpdating = True

End Sub

Hi GC Excel,

First - I really like your code, but I wondered about these things.

1. What if the user selects cells that don't intersect with column D? You give an error message so that is ok.
2. Was the question if the whole cell is "Cat1" or if "Cat1" is anywhere in the string in that cell?

Still - I like your code and learned from it. But I interpreted the question with the points above included.
If I wrote my code again, I'd add the Input string of words he is looking for. I liked that. We might also include a prompt for which column he is searching for the input words.

Suggest you post a sample workbook. You should know this by now. Don't expect people to make a test environment before preparing a solution for you.

Regards, TMS

Hi Yasser,

Here is your code:

Sub ForYasserKhalil()
    Dim CellRng As Range
    Dim BottomRow As Double
    Dim TopRow As Double
    Dim RowCtr As Double
    Dim WordList As Variant
    Dim ListCtr As Double
    
    WordList = Split("Cat1,Cat2,Cat3,Cat4,Cat5", ",")
    
    Set CellRng = Application.InputBox("Select a range of cells", Type:=8)
    ' Type:=8 lets you select a range of cells on the worksheet
    
    TopRow = CellRng.Cells.Row
    BottomRow = CellRng.Cells.Rows.Count + TopRow - 1

    For ListCtr = 0 To UBound(WordList)
        For RowCtr = BottomRow To TopRow Step -1
            If InStr(Cells(RowCtr, "D"), WordList(ListCtr)) > 0 Then
                Cells(RowCtr, "D").EntireRow.Delete Shift:=xlUp
                BottomRow = BottomRow - 1
            End If
        Next RowCtr
    Next ListCtr
End Sub

You will need to select a range of cells by clicking on the worksheet and dragging the mouse. This is Type:=8 input.
You should also look at the VBA code and add any "Cat" words you want searched for.

Hope this is what you wanted.

Thanks a lot for this great and useful help
You are awesome

All the solutions offered are wonderful
Thank you very much for this great and brilliant methods

@Marvin,

Thanks for the feedback...

2. Was the question if the whole cell is "Cat1" or if "Cat1" is anywhere in the string in that cell?

You are correct. I should have write this instead since the search value can be anywhere in the cell:

.AutoFilter 1, "*" &  ar(i) & "*"