excel - 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.

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.