I have searched all over the web to find out how to use Autofilter in Excel.
Many examples use one or two criteria, however none deal with the issue to autofilter on all values different from e.g. X, Y, Z (read: more than two criteria).
I found out how to use array in Autofilter, but I can not get it to invert the selection!?
EXAMPLE: The following works perfect
In column A I have row 2 to (almost infinite) showing A,B,C,D,E,F,G etc. (respectively)
in VBA: I filter on A,B,C & G
c1 = "A"
c2 = "B"
c3 = "C"
c4 = "G"
ActiveSheet.Range("$a$1:$y$1").AutoFilter Field:=1, Criteria1:= _
Array(c1,c2, c3,c4), Operator:=xlFilterValues
I know I can use Operator:=xlAnd (xlOr) if there are only two criteria. But it seems easier to use array.
My problem, - how do I invert the selection? (meaning select all values, except c1,c2,c3,c4 using array) ...this must be possible without looping through e.g. 20000 rows.
I tried:
c1 = "<>A"
c2 = "<>B"
c3 = "<>C"
c4 = "<>G"
ActiveSheet.Range("$a$1:$y$1").AutoFilter Field:=1, Criteria1:= _
Array(c1,c2, c3,c4), Operator:=xlFilterValues
...but it doesn't work. What am I missing?
Many examples use one or two criteria, however none deal with the issue to autofilter on all values different from e.g. X, Y, Z (read: more than two criteria).
I found out how to use array in Autofilter, but I can not get it to invert the selection!?
EXAMPLE: The following works perfect
In column A I have row 2 to (almost infinite) showing A,B,C,D,E,F,G etc. (respectively)
in VBA: I filter on A,B,C & G
c1 = "A"
c2 = "B"
c3 = "C"
c4 = "G"
ActiveSheet.Range("$a$1:$y$1").AutoFilter Field:=1, Criteria1:= _
Array(c1,c2, c3,c4), Operator:=xlFilterValues
I know I can use Operator:=xlAnd (xlOr) if there are only two criteria. But it seems easier to use array.
My problem, - how do I invert the selection? (meaning select all values, except c1,c2,c3,c4 using array) ...this must be possible without looping through e.g. 20000 rows.
I tried:
c1 = "<>A"
c2 = "<>B"
c3 = "<>C"
c4 = "<>G"
ActiveSheet.Range("$a$1:$y$1").AutoFilter Field:=1, Criteria1:= _
Array(c1,c2, c3,c4), Operator:=xlFilterValues
...but it doesn't work. What am I missing?
