Autofilter Array in VBA with does not equal

[tcnt9176]

I have searched all over looking for the solution without any luck. I have a filter that needs an array that has 3 criteria that I do not want to pull in my filter. Here are the ways I have tried to get this to work without success.

    Selection.AutoFilter Field:=3, Criteria1:=Array( _
        "<>400000001", "<>400000002", "<>400000004"), Operator:=xlFilterValues

    Selection.AutoFilter Field:=3, Criteria1:=<>Array( _
        "400000001", "400000002", "400000004"), Operator:=xlFilterValues

 

[texasalynn]

I would use macro record and see how it is recorded

 

[tcnt9176]

I am not at a computer right now but I'm pretty sure that the limit is 2 selections for autofiltering when you record.

 

[texasalynn]

Then a macro can not override that limit. You would have to create a helper column and filter on that. Perhaps create an if statement for the 3 items and return a 1 for true and 0 for false.

 

[tcnt9176]

ok. who would have thought that you can build an array to filter more than 2 if they are = but if they are <> you can't. Oh well...thanks for helping me.

 

[Weaver]

Have you had a look at the advanced filter?

You need to assemble your criteria list on another sheet, but it makes it easy to have many criteria.

 

[James Snyder]

Necro answer:

.AutoFilter Field:=wkColumn, Criteria1:=Array( _
param2, _
param3, _
param4), _
Operator:=xlFilterValues

The Operator is required when using an array. It isn't needed for a straight assignment.