Vba to delete entire row if cell got multiple wildcard conditions

[kit99]

I'm trying to delete entire row if cell in column I is NOT like "*loenn*" or not like "*lott*".
Both wildcard search and multiple conditions if to delete row.

Tried this:

    Last = Cells(Rows.Count, "A").End(xlUp).Row
    For i = Last To 2 Step -1
        If ((Cells(i, "I").Value <> "*loenn*") Or _
            (Cells(i, "I").Value <> "*lott*")) Then
            
            Cells(i, "I").EntireRow.Delete
        End If
    Next i

... but it deletes ALL rows!
Anyone that knows how I can get this vba to work?

And is there perhaps a way to to this faster? Maybe autofilter (I've never used this function)?

 

[wideboydixon]

The "<>" operator is "not equals" you probably need:

    Last = Cells(Rows.Count, "A").End(xlUp).Row
    For i = Last To 2 Step -1
        If ((Cells(i, "I").Value Like "*loenn*") Or _
            (Cells(i, "I").Value Like "*lott*")) Then
            ' Do Nothing
        Else
            Cells(i, "I").EntireRow.Delete
        End If
    Next i

WBD

 

[kit99]

The "<>" operator is "not equals" you probably need:

    Last = Cells(Rows.Count, "A").End(xlUp).Row
    For i = Last To 2 Step -1
        If ((Cells(i, "I").Value Like "*loenn*") Or _
            (Cells(i, "I").Value Like "*lott*")) Then
            ' Do Nothing
        Else
            Cells(i, "I").EntireRow.Delete
        End If
    Next i

WBD

Thanks, but no sigar...
Your vba also deletes every row except headers.
I know for a fact that in column I (column no 9) there are 6 rows holdig value/text "*loenn*" or "*lott*", and these rows I want to keep. Others to be deleted.

 

[wideboydixon]

I think you need to check the contents of those cells. I ran it locally and it did as expected. Is the text in the cells in lower case? Do you need to make it lower case before running the comparison?

    Last = Cells(Rows.Count, "A").End(xlUp).Row
    For i = Last To 2 Step -1
        If ((LCase(Cells(i, "I").Value) Like "*loenn*") Or _
            (LCase(Cells(i, "I").Value) Like "*lott*")) Then
            ' Do Nothing
        Else
            Cells(i, "I").EntireRow.Delete
        End If
    Next i

WBD

 

[kit99]

I think you need to check the contents of those cells. I ran it locally and it did as expected. Is the text in the cells in lower case? Do you need to make it lower case before running the comparison?

    Last = Cells(Rows.Count, "A").End(xlUp).Row
    For i = Last To 2 Step -1
        If ((LCase(Cells(i, "I").Value) Like "*loenn*") Or _
            (LCase(Cells(i, "I").Value) Like "*lott*")) Then
            ' Do Nothing
        Else
            Cells(i, "I").EntireRow.Delete
        End If
    Next i

WBD

Outch... If my PC could talk it would say: "Error located between keyboard and chair back"!
Your vba Works!
Thanks a lot for helping out.

 

[wideboydixon]

I think you mean PEBKAC

Glad it's working for you.

WBD