AutoFilter Excluding

[Rudi]

Hi,

Just need to confirm something:

I need to filter a large volume of data for 70 values. Instead of using:
Range("A1").AutoFilter Field:=1, Criteria1:=Array( <list all 70 items here> ...)

It would be easier to reverse it and write:
Range("A1").AutoFilter Field:=1, Criteria1:=Array( <list ONLY the 7 <>items here> ...)

According to websites, it is "said" that it is not possible to use the Array(...) and EXCLUDE. (I tried and the advice seems correct as my code debugs!)

The only other workaround i can think of is using a helper column and an =OR(... , ... , ...) function specifying the 7 <> items, then filtering for TRUE. This works and is my current solution, but is there another way that i have missed that allows to filter excluding (without using this helper columns and formula)?

TX

[HansV]

The information you found is correct. You do need a helper column with a formula for this.

[Rudi]

TX for that confirmation. Appreciated!

Maybe Microsoft can think of improving this scenario with a new Operator - something like this:
Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:=Array("Item1","Item2","Item3",...), Operator:=xlNotFilterValues

That would be great!!!

[HansV]

Yeah, but don't hold your breath...