Excel – VBA – How to pass an Array as Criteria in Autofilter

The following figure shows the data available:

DND_Filter_Original

If you need to filter say Oranges and Apples alone, you can either pass both criteria (Yes! I have avoided using - Mutliple criteria) or can try using an Array where you can pass multiple values:

Sub AutoFilter_Using_Arrays()
    
Dim oWS As Worksheet
On Error GoTo Err_Filter
Dim arCriteria(0 To 1) As String
Set oWS = ActiveSheet
arCriteria(0) = "Apple"
arCriteria(1) = "Orange"
oWS.UsedRange.AutoFilter Field:=2, Criteria1:=arCriteria, Operator:=xlFilterValues
Finally:
If Not oWS Is Nothing Then Set oWS = Nothing
Err_Filter:
If Err <> 0 Then
MsgBox Err.Description
Err.Clear
GoTo Finally
End If
End Sub

NOTE: If you leave out the Operator in Excel VBA Autofilter then only last value of the Array will be displayed.

You can also pass the values directly like:

WS.UsedRange.AutoFilter Field:=2, Criteria1:=Array("Apples","Peaches","Grapes), Operator:=xlFilterValues
SOURCE

LINK (Vbadud.blogspot.com)

LANGUAGE
ENGLISH