vba not like

[phairplay]

Hi can someone please help me.


I have the following vba that removes rows of data from the excel if one of the cells contains any of these words in the string

Dim LR As Long, i As Long
LR = Range("I" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
If Range("I" & i).Value Like "*good*" Or Range("I" & i).Value Like "*transit*" Or Range("I" & i).Value Like "*TRANSIT*" Or Range("I" & i).Value Like "*Good*" Or Range("I" & i).Value Like "*GOOD*" Or Range("I" & i).Value Like "*Transit*" Then Rows(i).delete
Next i

I would like to change this from a like to a not like. leaving every row that does contain these words, deleting everthing else.

can this be done, as i can't get it to work

 

[Andrew Poulsom]

Try:

    Dim LR As Long, i As Long
    LR = Range("I" & Rows.Count).End(xlUp).Row
    For i = LR To 2 Step -1
        If Not UCase(Range("I" & i).Value) Like "*GOOD*" And Not UCase(Range("I" & i).Value) Like "*TRANSIT*" Then Rows(i).Delete
    Next i