Excel VBA Autofilter - Specify Multiple Criteria using Array

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

After long time let us revisit our good old Autofilter Fruits example. The following figure shows the data available

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


If you leave out the Operator in Excel VBA Autofilter- Only Last Value of the Array Will be displayed

You can also pass the values directly like:


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


AutoFilter using Excel VBA

AutoFilter using Excel VBA

Filters are one of the most used Excel utilities. Here is a simple way to create a filter through code

Sub Simple_AutoFilter()

Dim oWS As Worksheet

On Error GoTo Err_Filter

oWS = ActiveSheet

oWS.UsedRange.AutoFilter Field:=2, Criteria1:="Apple"

Finally:

If Not oWS Is Nothing Then oWS = Nothing

Err_Filter:

If Err <> 0 Then

MsgBox(Err.Description)

Err.Clear

GoTo Finally

End If

End Sub

Range Before Filtering
Range after Filter




See also:

Create AutoFilter with Multiple Criteria using Excel VBA

AutoFilter using Excel VBA

Check for existence of Filter using Excel VBA

Excel Filter Show All using VBA

Retrieve / Get First Row of Excel AutoFilter using VBA