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.
You do not need to transpose a single element from an array and you cannot put criteria into the 5th 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.