How to filter using the NOT Array?

[sourabh_ajmera]

Hello Everybody,

I am stuck at a code where I would like to use a NOT function on filters with array. I want to use the 'not' function to not filter these 5 values from a list of 500 values

    Sheets("Instrument").Select
    ActiveSheet.Range("A1").CurrentRegion.AutoFilter Field:=6, Criteria1:="US Branch"
    ActiveSheet.Range("A1").CurrentRegion.AutoFilter Field:=3, Criteria1:=<>Array( _              <-- I have also tried ....Criteria1:=Array("<>1234", "<876428>"...so on..)
    "123456", "8654343", "5678334", "4624663", "67882542"), Operator:=xlFilterValues
    Range("A55").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.EntireRow.Delete
    ActiveSheet.Range("A1").CurrentRegion.AutoFilter Field:=3
    ActiveSheet.Range("A1").CurrentRegion.AutoFilter Field:=6
    Range("B2").Select

Thank you for your time and help.

 

[Andrew Poulsom]

You can't use not equals with a Values Filter, sorry. That applies to <ACRONYM title="visual basic for applications">VBA</ACRONYM> and the user interface.

 

[sourabh_ajmera]

Hmmmm...coz I thought since it works for a single value (not in array) I am doing something wrong. Always great to learn something new. Thank You!
Is there a work around you would suggest?

 

[Andrew Poulsom]

You could use Advanced Filter.