How to filter using the NOT Array?

sourabh_ajmera

New Member
Joined
Jul 17, 2014
Messages
36
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

Rich (BB code):
    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.
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

New Member
Joined
Jul 17, 2014
Messages
36
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,109,549
Messages
5,529,467
Members
409,883
Latest member
asharris90
Top