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

Get FREE Advanced Excel Exercises with Solutions!

If you are looking for ways to filter with multiple criteria array in Excel VBA, then you are in the right place. Filtering a large dataset based on multiple criteria can be made easier with the help of VBA codes rather than using the conventional feature of Excel.
So, let’s start our main article.


Filter with Multiple Criteria in an Array Using Excel VBA: 7 Ways

In the following dataset, we have some records of marks corresponding to the names of the students and their IDs. We will try to filter this dataset based on different criteria as an array by using some codes.

Excel VBA filter multiple criteria array

We have used Microsoft Excel 365 version here, you can use any other version at your convenience.


Method-1: Filter with Multiple Criteria as Texts in Array

Here, we will try to filter the following dataset based on the Student Name column for multiple criteria containing the strings Emily, Daniel, and Gabriel in an array.

Excel VBA filter multiple criteria array

Step-01:
➤ Go to the Developer Tab >> Visual Basic Option.

texts

Then, the Visual Basic Editor will open up.
➤ Go to the Insert Tab >> Module Option.

Excel VBA filter multiple criteria array

After that, a Module will be created.

texts

Step-02:
➤ Write 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.

texts

➤ Press F5.
Then, you will have the dataset filtered down for multiple criteria to show the names of the students and their corresponding Ids and Marks for the students Emily, Daniel, and Gabriel.

Excel VBA filter multiple criteria array

Read More: Filter Multiple Criteria in Excel with VBA (Both AND and OR Types)


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

Here, we will be filtering down the following dataset for the IDs 101135, 101137, and 101138 by using these numbers as multiple criteria in an array.

Excel VBA filter multiple criteria array

Steps:
➤ Follow Step-01 of Method-1.
➤ Write 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 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’ IDs such as 101135, 101137, and 101138 and we have put them inside inverted commas to specify them as strings because AutoFilter will work for only an array of strings.

numbers

➤ Press F5.
After that, you will get the names and marks of the students having ids 101135, 101137, and 101138.

numbers


Method-3: Setting Multiple Criteria in a Range for Using as an Array

Here, we have listed the criteria in the List column containing the IDs 101134, 101135, and 101136 based on which we will do our filtering process.

Excel VBA filter multiple criteria array

Steps:
➤ Follow Step-01 of Method-1.
➤ Write 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 having the lower limit and upper limit we used the LBOUND function and UBOUND function respectively.
The FOR loop is used for converting 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.

Multiple criteria in a range

➤ Press F5.
Afterward, you will get the names and marks of the students having IDs 101134, 101135, and 101136.

Multiple criteria in a range

Read More: How to Filter Based on Cell Value Using Excel VBA


Method-4: Using SPLIT and JOIN Functions for Creating Array with Multiple Criteria

Here, we will utilize the following list in the List column as an array and for filtering the dataset properly we will also use the SPLIT function, JOIN function, and TRANSPOSE function in a VBA code.

Excel VBA filter multiple criteria array

Steps:
➤ Follow Step-01 of Method-1.
➤ Write the following code

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 join each of the values into an array of strings, and finally, SPLIT will break down each string to give input them separately as criteria for filtering the dataset.

SPLIT, JOIN functions

➤ Press F5.
Finally, you will get the names and marks of the students having IDs 101134, 101135, and 101136.

SPLIT, JOIN functions

Read More: How to Remove Filter in Excel VBA


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

In this section, we will be filtering down the following dataset depending on the Student Id column for multiple criteria as listed in the List column.

Excel VBA filter multiple criteria array

Steps:
➤ Follow Step-01 of Method-1.
➤ Write the following code

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

Here, 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 here 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 have used this array as Criteria1 for AutoFilter.

multiple criteria in a loop

➤ Press F5.
Eventually, you will get the names and marks of the students having ids 101134, 101135, and 101136.

multiple criteria in a loop


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.

Excel VBA filter multiple criteria array

Steps:
➤ Follow Step-01 of Method-1.
➤ Write the following code

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.

Named Range

➤ Press F5.
Then, 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.

Named Range

Read More: Excel VBA to Filter in Same Column by Multiple Criteria


Method-7: Filter Table with Multiple Criteria in an Array

Here, we have the following Table whose name is Table1 and using Excel VBA we will try to filter down this table based on the names Emily, Daniel, and Gabriel as multiple criteria in an array.

Excel VBA filter multiple criteria array

Steps:
➤ Follow Step-01 of Method-1.
➤ Write the following code

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.

Table

➤ Press F5.
Eventually, 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 Emily, Daniel, and Gabriel.

Table


Practice Section

For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

practice


Download Workbook


Conclusion

In this article, we tried to cover the ways to filter with multiple criteria as an array using Excel VBA easily. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Tanjima Hossain
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo