CODEWITHSUNDEEP
vbaexcelfilterautofilter
Lewis Heslop
Lewis Heslop

Reputation: 590

How to AutoFilter or similar to include only certain words

I understand I can use auto filter like this to remove certain rows that contain certain strings, as shown below (in this example the removed row contains string "111-11111"). 

Set ws = Sheets("Gate_Results")
Set rng1 = ws.Range(ws.[u1], ws.Cells(Rows.Count, "U").End(xlUp))
With ActiveSheet
    .AutoFilterMode = False
    rng1.AutoFilter Field:=1, Criteria1:="111-11111"
    rng1.Offset(1, 0).EntireRow.Delete
    .AutoFilterMode = False
End With

However I was wondering if there was a way to remove strings that DO NOT contain the criteria. An example of this would be if string ###-##### represents product codes, if I want to filter my list to see only 3 different products out of a library of codes, could I use the AutoFilter function to do so, and if so how?

I tried the following (and similar example but &-ing in additional strings in the criteria1 section) already, however it did not work at all, but that was just my amateurish guesswork.

Set ws = Sheets("Gate_Results")
Set rng1 = ws.Range(ws.[u1], ws.Cells(Rows.Count, "U").End(xlUp))
With ActiveSheet
    .AutoFilterMode = False
    rng1.AutoFilter Field:=1, Criteria1:<>"111-11111"
    rng1.Offset(1, 0).EntireRow.Delete
    .AutoFilterMode = False
End With

I'd appreciate any help on finding a working method, even if it doesn't involve autofilter. But efficiency would be extra appreciated. :D

Upvotes: 1

Views: 1612

Answers (1)

Nat Aes
Nat Aes

Reputation: 927

You need to put the <> between the inverted commas. So:

Set ws = Sheets("Gate_Results")
Set rng1 = ws.Range(ws.[u1], ws.Cells(Rows.Count, "U").End(xlUp))
With ActiveSheet
    .AutoFilterMode = False
    rng1.AutoFilter Field:=1, Criteria1:"<>111-11111"
    rng1.Offset(1, 0).EntireRow.Delete
    .AutoFilterMode = False
End With

Upvotes: 1

Related Questions