Excel VBA: How to Filter with Multiple Criteria in Array (7 Ways)

In the following dataset, we have some records of students as well as their IDs and marks. We will filter this dataset based on different criteria as an array with code.

Method 1 – Filter with Multiple Criteria as Texts in Array

Let’s filter the following dataset based on the Student Name column for multiple criteria containing the strings Emily, Daniel, and Gabriel in an array.

Steps:

Go to the Developer tab and select Visual Basic.

The Visual Basic Editor will open up.
Go to the Insert tab and select Module.

A Module will be created.

Copy the following code:

Sub filter_with_array_as_criteria_1()

ActiveSheet.Range("B3:D3").AutoFilter Field:=2, _
Operator:=xlFilterValues, Criteria1:=Array("Emily", "Daniel", "Gabriel")

End Sub

Here, we declared the header names in the range B3:D3 in which we will apply the filter and Field:=2 is the column number of this range based on which we will do this filtering process.
Finally, we have set the criteria as an array for declaring multiple students’ names such as Emily, Daniel, and Gabriel.

Press F5.
The code filters the dataset to show the names of the students and their corresponding Ids and Marks for the students Emily, Daniel, and Gabriel.

Method 2 – Filter with Multiple Number Criteria in an Array Using Excel VBA

Let’s filter the following dataset for the IDs 101135, 101137, and 101138 by using these numbers as multiple criteria in an array.

Steps:

Open a VBA module (follow the steps in Method 1).
Copy the following code:

Sub filter_with_array_as_criteria_2()

ActiveSheet.Range("B3:D3").AutoFilter Field:=1, Operator:=xlFilterValues, _
Criteria1:=Array("101135", "101137", "101138")

End Sub

Here, we declared the header names in the range B3:D3 in which we will apply the filter and Field:=2 as the column number of the range to use as the filter.
Finally, we have set the criteria as an array for declaring multiple students’ IDs such as 101135, 101137, and 101138 and have put them inside inverted commas to specify them as strings because AutoFilter will work for only an array of strings.

Press F5.
You will get the names and marks of the students whose IDs are 101135, 101137, and 101138.

Method 3 – Setting Multiple Criteria in a Range for Use as an Array

We have listed the criteria in the List column, containing the IDs 101134, 101135, and 101136, which we will use for the filtering process.

Steps:

Open the VBA Module editor by following Method 1.
Copy the following code:

Sub filter_with_array_as_criteria_3()

Dim ID_range, k As Variant
ID_range = Application.Transpose(ActiveSheet.Range("F4:F6"))

For k = LBound(ID_range) To UBound(ID_range)
ID_range(k) = CStr(ID_range(k))
Next k
ActiveSheet.Range("B3:D3").AutoFilter Field:=1, Operator:=xlFilterValues, _
Criteria1:=ID_range

End Sub

Here, we have declared ID_range, k as Variant and ID_range is the array that will store multiple criteria, and k is the increment ranging from the lower limit to the upper limit of this array. For the lower limit and upper limit, we used the LBOUND function and UBOUND function, respectively.
The FOR loop is used to convert the values other than strings in the array into strings with the help of the CStr function. Finally, we have utilized this array as Criteria1.

Press F5.
You will get the names and marks of the students with IDs 101134, 101135, and 101136.

Method 4 – Using SPLIT and JOIN Functions for Creating an Array with Multiple Criteria

Let’s use a similar list in a column to filter the table.

Steps:

Open the VBA module (follow Method 1).
Copy the following code into the module:

Sub filter_with_array_as_criteria_4()

ActiveSheet.Range("B3:D3").AutoFilter Field:=1, Operator:=xlFilterValues, _
Criteria1:=Split(Join(Application.Transpose(Range("F4:F6")), ","), ",")

End Sub

Here, TRANSPOSE will convert the 2D array into a 1D array. Otherwise, AutoFilter will not work. JOIN will then join each of the values into an array of strings, and SPLIT will break down each string to input them separately as criteria for filtering the dataset.

Press F5.
You will get the names and marks of the students whose IDs are 101134, 101135, and 101136.

Read More: How to Remove Filter in Excel VBA

Method 5 – Filter with Multiple Criteria in a Loop for Array with VBA

We will be filtering down the following dataset depending on the Student Id column for multiple criteria as listed in the List column.

Steps:

Open the VBA module (follow Method 1).
Copy the following code into the module:

Sub filter_with_array_as_criteria_5()

Dim k As Integer
Dim ID_range(100) As String

For k = 4 To 6
ID_range(k) = ActiveSheet.Range("F" & k)
Next k
ActiveSheet.Range("B3:D3").AutoFilter Field:=1, Operator:=xlFilterValues, _
Criteria1:=ID_range

End Sub

We have declared k as an Integer, ID_range(100) as a String where ID_range is an array that will store up to 100 values. To determine the values for this array, we have used the FOR loop for k from 4 to 6 as the row numbers of the List column and F is the column name.
Finally, we’re using this array as Criteria1 for AutoFilter.

Press F5.
You will get the names and marks of the students based on the filter values.

Method 6 – Using Named Range for Multiple Criteria

Here, we have listed some names of the students in the List column and named this range as Student. Using this named range we will define an array that will contain multiple criteria for the AutoFilter feature.

Steps:

Open the VBA module (follow Method 1).
Copy the following code into the module:

Sub filter_with_array_as_criteria_6()

Dim Student_range, k As Variant
Student_range = Application.Transpose(ActiveSheet.Range("Student"))
ActiveSheet.Range("B3:D3").AutoFilter Field:=2, _
Operator:=xlFilterValues, Criteria1:=Student_range

End Sub

Here, we have declared Student_range, k as a Variant, and used the TRANSPOSE function to convert the 2D array of the named range Student into a 1D array and then stored it in Student_range. Then, it is used as Criteria1 for the AutoFilter method.

Press F5.
You will have the dataset filtered down for multiple criteria to show the name of the students and their corresponding Ids and Marks for the students Jefferson, Emily, and Sara.

Method 7 – Filter Table with Multiple Criteria in an Array

We will try filter down this table based on the names Emily, Daniel, and Gabriel as multiple criteria in an array.

Steps:

Open the VBA module (follow Method 1).
Copy the following code into the module:

Sub filter_with_array_as_criteria_7()

ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=2, _
Operator:=xlFilterValues, Criteria1:=Array("Emily", "Daniel", "Gabriel")

End Sub

Here, ListObjects(“Table1”) is used for defining the table Table1, Field:=2 for setting up the second column of this range as a base of the filtering process and finally we have defined an array containing multiple names for Criteria1.

Press F5.
The dataset is filtered to show only the name of the students and their corresponding Ids and Marks for Emily, Daniel, and Gabriel.