Delete rows based on not equal to with wild card

[Apple08]

Hi All

I need to delete some rows which are not equal to certain text including wildcard as words can be attached with other text as well. However the macro doesn't work, please could anyone help? Thanks.

Private Sub RemoveRows()

    Sheet1.Select
    Dim x As Long, LastRow As Long

    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    Range("B:B").Delete

    For x = LastRow To 2 Step -1
        If Cells(x, 2).Value <> "*Apple*" And Cells(x, 2).Value <> "*Orange*" And Cells(x, 2).Value <> "*Banana*" Then
            Rows(x).Delete
        End If
       
    Next x


End Sub

 

[Joe4]

Try changing this line:

If Cells(x, 2).Value <> "*Apple*" And Cells(x, 2).Value <> "*Orange*" And Cells(x, 2).Value <> "*Banana*" Then

to this:

If InStr(LCase(Cells(x, 2)), "apple") = 0 And InStr(LCase(Cells(x, 2)), "orange") = 0 And InStr(LCase(Cells(x, 2)), "banana") = 0 Then

 

[Apple08]

If InStr(LCase(Cells(x, 2)), "apple") = 0 And InStr(LCase(Cells(x, 2)), "orange") = 0 And InStr(LCase(Cells(x, 2)), "banana") = 0 Then

Thanks Joe4, it works perfectly! Please is the macro case sensitive as I noticed you have put all words in lower case. Thanks.

 

[Joe4]

The "LCase" function takes the value and changes it all to lower case. I did this so the code is NOT case sensitive.
It will work with values like "Apple", "APPLE", or "apple", etc.

 

[Apple08]

The "LCase" function takes the value and changes it all to lower case. I did this so the code is NOT case sensitive.
It will work with values like "Apple", "APPLE", or "apple", etc.

Thanks for the explaination.