Filter Multiple Criteria in Excel with VBA: 2 Easy Methods 

Method 1 – Filter Multiple Criteria of AND Type in Excel with VBA

Develop a Macro to filter multiple criteria of AND type for any data set.

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 used Cells(i, 2) = “Novel” and Selection.Cells(i, 2) >=25.

VBA Code to Filter Multiple Criteria in Excel with VBA

  • 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.
  • Change these according to your needs.
  • If you want books with 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.
  • In line 6, we used Range(“F4”) because we wanted the filtered data to be started from cell F4.

  • Change it accordingly.

Method 2 – Filter Multiple Criteria of OR Type in Excel with VBA

Develop a Macro to filter multiple criteria of OR type for any data set.

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

Start from cell F4 and filter the novels or books with prices greater than $25.00 in a new range.

⧭ Notes:

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

VBA Code to Filter Multiple Criteria in Excel with VBA

  • 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.
  • Change these according to your needs.
  • In line 6, we used Range(“F4”) because we wanted the filtered data to be started from cell F4.


Things to Remember

You can use as many criteria as you like. 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.


Further Readings

Get FREE Advanced Excel Exercises with Solutions!
Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo