Using string array as criteria in VBA autofilter

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 Variantarr = 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:=xlFilterValuesEnd With

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

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 Variantarr = Array("Alpha", "Bravo", "Charlie")Sheet17.Range("E1").AutoFilter _    Field:=5, _    Criteria1:=arr, _    Operator:=xlFilterValues