Select each row one by one and check for a substring in each cell of it

Originally Posted by Trebor76

Hi Abhushan86,

Welcome to the forum!!

As well as jraj1106's clever solution here's my attempt (which may also help anyone who is unable to download from the Internet):

Option Explicit
Sub Macro1()

    'http://www.excelforum.com/excel-programming/827684-select-each-row-one-by-one-and-check-for-a-substring-in-each-cell-of-it.html
    
    Dim lngLastRow As Long, _
        lngMyRow As Long
    Dim strMyText As String
    Dim strMyCols As String, _
        strMyColFrom As String, _
        strMyColTo As String
    Dim varMyCol As Variant
    
    Application.ScreenUpdating = False
    
    strMyCols = "D:E" 'Columns to be checked for 'strMyText'. Change to suit.
    strMyText = "Good morning" 'Text to be search in 'strMyCols'. Change to suit.
    
    lngLastRow = Range(strMyCols).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    For Each varMyCol In Split(strMyCols, ":")
        If strMyColFrom = "" Then
            strMyColFrom = varMyCol
        Else
            strMyColTo = varMyCol
        End If
    Next varMyCol
    
    For lngMyRow = lngLastRow To 2 Step -1
        If Evaluate("COUNTIF(" & strMyColFrom & lngMyRow & ":" & strMyColTo & lngMyRow & ",""*" & strMyText & "*"")") > 0 Then
            Rows(lngMyRow).EntireRow.Delete
        End If
    Next lngMyRow
    
    Application.ScreenUpdating = True
    
    MsgBox "Any applicable rows have now been deleted."
    
End Sub

HTH

Robert

Thanks for the help Robert. \m/