Delete rows that do no contain a specific word

Hi Tesseh (and fellow Sydneyite ),

Try this (initially on a copy of your data as results cannot be undone if they're not as expected):

Option Explicit
Sub Macro2()

    'Written by Trebor76
    'Visit my website wwww.excelguru.net.au
    
    'Delete all rows where the text 'DOGS' is not in Col. B from the row number passed to the constant variable 'lngStartRow'.
    
    Const lngStartRow As Long = 2 'Starting row number for your data. Change to suit.
    
    Dim lngMyCol As Long, _
        lngMyRow As Long
    Dim xlnCalcMethod As XlCalculation
            
    With Application
        xlnCalcMethod = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    lngMyCol = Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
    lngMyRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    With Columns(lngMyCol)
        With Range(Cells(lngStartRow, lngMyCol), Cells(lngMyRow, lngMyCol))
            .Formula = "=IFERROR(FIND(""DOGS"",B" & lngStartRow & "),NA())"
            ActiveSheet.Calculate
            .Value = .Value
        End With
        On Error Resume Next 'Turn error reporting off - OK to ignore 'No cells found' message
            .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
        On Error GoTo 0 'Turn error reporting back on
        .Delete
    End With
    
    With Application
        .Calculation = xlnCalcMethod
        .ScreenUpdating = True
    End With

    MsgBox "All applicable rows have now been deleted.", vbInformation, "Excel Guru"

End Sub

Note that the code is looking for the text 'DOGS' in that case. If your not worried about case sensitivity (i.e. 'Dogs', 'dogs', etc...), change this line of code...

.Formula = "=IFERROR(FIND(""DOGS"",B" & lngStartRow & "),NA())"

...to this:

.Formula = "=IFERROR(SEARCH(""DOGS"",B" & lngStartRow & "),NA())"

Regards,

Robert

No, I didn't think you ever did but that's fine - you have now

If you could mark the thread as closed it would be appreciated.

Thanks,

Robert