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

Get FREE Advanced Excel Exercises with Solutions!

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.

Data Set to Filter Multiple Criteria in Excel with VBA

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

Read More: How to Filter Based on Cell Value Using Excel 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

VBA Code to Filter Multiple Criteria in Excel of AND Type

⧭ 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.

Output to Filter Multiple Criteria in Excel with VBA

⧭ Notes:

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

VBA Code to Filter Multiple Criteria in Excel with VBA

  • 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.

Read More: Excel VBA: How to Filter with Multiple Criteria in Array


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

VBA Code to Filter Multiple Criteria in Excel with VBA

⧭ Output:

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

Running Code to Filter Multiple Criteria in Excel with VBA

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.

VBA Code to Filter Multiple Criteria in Excel with VBA

  • 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.


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.


Further Readings

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan
Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo