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

In this article, I’ll show you how to filter multiple criteria from a dataset with VBA in Excel. You’ll learn to filter both AND type and OR type multiple criteria.

Filter Multiple Criteria in Excel with VBA: 2 Easy Methods

Here we’ve got a data set with the names, book types, and prices of a bookshop called Martin Bookstore.

Our objective today is to filter multiple criteria from this data set with Visual Basic of Applications (VBA).

1. Filter Multiple Criteria of AND Type in Excel with VBA

First, we’ll develop a Macro to filter multiple criteria of AND type for any data set.

For example, let’s try to filter out the books that are novels and have a price greater than $25.00.

⧭ VBA Code:

Sub Filter_Multiple_Criteria_AND_Type()

Count = 1
For i = 1 To Selection.Rows.Count

    If Selection.Cells(i, 2) = "Novel" And Selection.Cells(i, 3) >= 25 Then

        For j = 1 To Selection.Columns.Count

            Range("F4").Cells(Count, j) = Selection.Cells(i, j)

        Next j

        Count = Count + 1

    End If

Next i

End Sub

⧭ Output:

Select the data set from the worksheet and run this Macro (Filter_Multiple_Criteria_AND_Type).

It’ll filter the books that are novels and have prices greater than $25.00 in a new range starting from cell F4.

⧭ Notes:

In the 4th line of the code, we’ve used Cells(i, 2) = “Novel” and Selection.Cells(i, 2) >=25.

Here we wanted the value from the 2nd column (Book Type) to be equal to “Novel” and that from the 3rd column (Price) to be greater than or equal to $25.00.
You change these according to your needs.
For example, if you want books that have prices greater than or equal to $20.00 but less than or equal to $30.00, use Selection.Cells(i, 3) >=20 and Selection.Cells(i, 3) <=30.
Also in line 6, we’ve used Range(“F4”) because we wanted the filtered data to be started from cell F4.

You change it accordingly.

2. Filter Multiple Criteria of OR Type in Excel with VBA

Next, we’ll develop a Macro to filter multiple criteria of OR type for any data set.

For example, let’s try to filter out the books that are novels or have a price greater than $25.00 this time.

⧭ VBA Code:

Sub Filter_Multiple_Criteria_Or_Type()

Count = 1

For i = 1 To Selection.Rows.Count

    If Selection.Cells(i, 2) = "Novel" Or Selection.Cells(i, 3) >= 25 Then

        For j = 1 To Selection.Columns.Count

            Range("F4").Cells(Count, j) = Selection.Cells(i, j)

        Next j

        Count = Count + 1

    End If

Next i
End Sub

⧭ Output:

Select the data set from the worksheet and run this Macro (Filter_Multiple_Criteria_OR_Type).

It’ll filter the books that are novels or have prices greater than $25.00 in a new range starting from cell F4.

⧭ Notes:

In the 4th line of the code, we’ve used Selection.Cells(i, 2) = “Novel” or Selection.Cells(i, 2) >=25.

Here we wanted the value from the 2nd column (Book Type) to be equal to “Novel” or that from the 3rd column (Price) to be greater than or equal to $25.00.
You change these according to your needs.
Also in line 6, we’ve used Range(“F4”) because we wanted the filtered data to be started from cell F4.

Read More: How to Remove Filter in Excel VBA

Things to Remember

Here we’ve used two criteria for multiple criteria. You can use as many criteria as you like. Just join all the criteria with And or Or according to the condition.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Filter Multiple Criteria with VBA.xlsm

Conclusion

Using these methods, you can filter multiple criteria in Excel with VBA, of both AND and OR types. Do you have any questions? Feel free to ask us.