Referencing multiple cells VBA Autofilter

Question

I am using the following code to apply an autofilter to a range and filter on one of the columns by looking for the same value that is contained in several reference cells:

Sub filter()

Range("B6:N9000").AutoFilter Field:=2, Criteria1:=Array(Range("C2").Value, Range("D2").Value, Range("E2").Value )

End Sub

The problem however is the filter only applies the LAST cell referenced in the code, ie for above it ONLY looks up "E2", not "C2" & "D2" & "E2"

Any suggestions? Thanks

Are you filtering for values that match at least one of C,D,E2 or for values that must fulfill all three? — Verzweifler
Hi @Verzweifler I'm searching for values that match at least 1 of C2, D2, E2 — JS142
Please try adding Operator:=xlFilterValues to your call and see if that works! — Verzweifler
@JS142 - I googled Operator:=xlFilterValues and had an answer in less time than it took to type this sentence. — user4039065
Thanks for the constructive comment @Jeeped . Have a good day — JS142

Verzweifler Verzweifler · Accepted Answer · 2015-09-25T11:42:08

To put an answer under this: You want to add the argument Operator:=xlFilterValues to your call, so it will look like this:

Range("B6:N9000").AutoFilter Field:=2, Criteria1:=Array(Range("C2").Value, _
    Range("D2").Value, Range("E2").Value), Operator:=xlFilterValues

or

[B6:N9000].AutoFilter Field:=2, _
    Criteria1:=Array([C2].Value, [D2].Value, [E2].Value), Operator:=xlFilterValues

Referencing multiple cells VBA Autofilter

1 Answers