Search code examples
excelcellautofiltervba

use multiple cell values in autofilter criteria

Please help me with this condition:

1) The value present in cell P1,P2,P3,P4 is user defined words like "F","S" and NA...

2) Use the user defined value as criteria for filtering the data in range(Y2:Y2999)

The thing I tried that didn't work is:

Sub c1()
Dim a As String
Dim b As String
Dim c As String
Dim d As String
a = Cells(1, 16).Value
b = Cells(2, 16).Value
c = Cells(3, 16).Value
d = Cells(4, 16).Value
ActiveSheet.Range("$Y$2:$Y$2999").AutoFilter Field:=1,_
Criteria1:=Array(a, b, c, d)
End Sub
Solution

  • I believe you should add a Criteria2, Criteria3, and Criteria4.

    I would update this line

    ActiveSheet.Range("$Y$2:$Y$2999").AutoFilter Field:=1,_
Criteria1:=Array(a, b, c, d)

    To be

    ActiveSheet.Range("$Y$2:$Y$2999").AutoFilter Field:=1, _
Criteria1:="=" & a, _
    Operator:=xlOr, _
Criteria2:="=" & b, _
    Operator:=xlOr, _
Criteria3:="=" & c, _
    Operator:=xlOr, _
Criteria4:="=" & d

    Or if you are going to do it like so:

    ActiveSheet.Range("$Y$2:$Y$2999").AutoFilter Field:=1,_
Criteria1:=Array(a, b, c, d)

    You should try adding the xlFilterValues operator so it would look like this:

    ActiveSheet.Range("$Y$2:$Y$2999").AutoFilter Field:=1, _
Criteria1:=Array(a, b, c, d), _
Operator:=xlFilterValues