Using string array as criteria in VBA autofilter

Question

I've searched other posts and found similar issues but nothing that could help me specifically. I'm trying to take an array of strings and use it as a filter criteria. It's tricky because the array is created by a function and has a variable number of elements and contents. I need the autofilter to take it and check column E for each one of its elements.

I've tried it two ways

1)

With Sheet17

    .Range("E1").AutoFilter Field:=5, Criteria1:=Application.Transpose(arr)

End With

Result: Applies a filter to column E but fails to select any of the options

2)

For i = 0 To counter - 1

    With Sheet17

        .Range("E1").AutoFilter Field:=5, Criteria1:=Application.Transpose(arr(i))

    End With

Next

Note: Counter is an integer representing the number of elements in the array.

Result: This one correctly loops through the array but only selects the last option on the filter - presumably because every time it loops back through it starts over and unchecks every other option so by the end only the most recent option remains checked.

Spat

Answer 1

You do not need to transpose a single element from an array and you cannot put criteria into the 5^th field if you are only referencing column E.

Dim i As Long, arr As Variant
arr = Array(1, 3)

With Sheet17
    'to filter each value in the array one at a time
    For i = 0 To UBound(arr)
        .Columns("E").AutoFilter Field:=1, Criteria1:=arr(i)
    Next i

    'my values were numbers - AutoFilter likes strings in its array
    For i = LBound(arr) To UBound(arr)
        arr(i) = CStr(arr(i))
    Next i

    'to filter all values in the array at once specify xlFilterValues
    .Columns("E").AutoFilter Field:=1, Criteria1:=arr, _
                             Operator:=xlFilterValues
End With

Specify the Operator:=xlFilterValues when passing an array and the Range.AutoFilter Method likes strings as the values in an array.

Collinsworth

Answer 2

The Excel documentation for AutoFilter provides some guidance. The Operator parameter takes a XlAutoFilterOperator that specifies how Criteria1 is interpreted. In your case, specifying a value of xlFilterValues will cause Criteria1 to be properly interpreted as an array of filter values.

The following example demonstrates this:

Dim arr As Variant
arr = Array("Alpha", "Bravo", "Charlie")

Sheet17.Range("E1").AutoFilter _
    Field:=5, _
    Criteria1:=arr, _
    Operator:=xlFilterValues

Claustral