VBA for deleting if NOT containing a given keyword

[etrader58]

I am looking for a VBA code to remove rows in which, column B does NOT contain "keyword1". The content of cells in column B is simple text such as "This is the text containing keyword1 in this cell".

 

[JackDanIce]

Something I use regularly.

Filter your data to show only the rows you want deleted and then run (it won't delete the header row):

Sub DeleteVisibleRows()
With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
End With
With ActiveSheet
    .UsedRange.Offset(1, 0).SpecialCells(xlCellTypeVisible).Rows.Delete
    .AutoFilterMode = False
End With
With Application
    .ScreenUpdating = True
    .DisplayAlerts = True
End With
End Sub

 

[VoG]

Try like this

Sub test2()
Dim LR As Long, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    If Not Range("B" & i).Value Like "*keyword1*" Then Rows(i).Delete
Next i
End Sub

 

[etrader58]

works like a charm