VBA: How to Use AutoFilter with Multiple Criteria


You can use the following methods to use AutoFilter in VBA with multiple criteria:

Method 1: Use AutoFilter with Multiple Criteria in One Column

Sub FilterMultipleCriteria()

With Range("A1:C11")
    .AutoFilter Field:=1, Criteria1:=Array("A", "C"), Operator:=xlFilterValues
End With

End Sub

This particular macro will filter all rows in the range A1:C11 where the value in the first column is equal to either “A” or “C”.

Method 2: Use AutoFilter with Multiple Criteria in Multiple Columns

Sub FilterMultipleCriteria()

With Range("A1:C11")
    .AutoFilter Field:=1, Criteria1:="A"
    .AutoFilter Field:=2, Criteria1:="Guard"
End With

End Sub

This particular macro will filter the range A1:C11 to only display rows where the first column is equal to “A” and the second column is equal to “Guard”.

The following examples show how to use each of these methods in practice with the following dataset in Excel:

Example 1: Use AutoFilter with Multiple Criteria in One Column

Suppose we would like to filter the dataset to only display rows where the first column is equal to either “A” or “C”.

We can create the following macro to do so:

Sub FilterMultipleCriteria()

With Range("A1:C11")
    .AutoFilter Field:=1, Criteria1:=Array("A", "C"), Operator:=xlFilterValues
End With

End Sub

When we run this macro, we receive the following output:

Notice that the dataset has been filtered to only show the rows where the first column is equal to either “A” or “C”.

Note: We only included two possible values in the Array() function but feel free to include as many as you’d like.

Example 2: Use AutoFilter with Multiple Criteria in Multiple Columns

Suppose we would like to filter the dataset to only show rows where the value in the Team column is equal to A and the value in the Position column is equal to Guard.

We can create the following macro to do so:

Sub FilterMultipleCriteria()

With Range("A1:C11")
    .AutoFilter Field:=1, Criteria1:="A"
    .AutoFilter Field:=2, Criteria1:="Guard"
End With

End Sub

When we run this macro, we receive the following output:

Noticed that the dataset has been filtered to only show rows where the value in the Team column is equal to A and the value in the Position column is equal to Guard.

Note: You can find the complete documentation for the VBA AutoFilter method here.

Additional Resources

The following tutorials explain how to perform other common tasks in VBA:

VBA: How to Filter Pivot Tables
VBA: How to Clear Filters
VBA: How to Remove Duplicate Values

3 Replies to “VBA: How to Use AutoFilter with Multiple Criteria”

  1. Hi Zach,

    i want to autofilter in a single columns with target value from range (A2:A100) from different worksheet. can you help me how to do it ?

    1. Hi newbie…Certainly! Using VBA to apply AutoFilter with multiple criteria from a range in a different worksheet can be done by reading the criteria into an array and then applying the AutoFilter with that array. Here’s how you can achieve this:

      ### Steps
      1. **Read the criteria from the range in the source worksheet into an array.**
      2. **Apply the AutoFilter on the target worksheet using the criteria array.**

      ### Example Code
      Here’s a complete example:

      “`vba
      Sub ApplyAutoFilterWithMultipleCriteria()
      Dim wsSource As Worksheet
      Dim wsTarget As Worksheet
      Dim criteriaRange As Range
      Dim criteriaArray() As Variant
      Dim lastRow As Long
      Dim i As Long

      ‘ Set references to the source and target worksheets
      Set wsSource = Worksheets(“SourceSheetName”) ‘ Replace with the name of your source sheet
      Set wsTarget = Worksheets(“TargetSheetName”) ‘ Replace with the name of your target sheet

      ‘ Define the range containing the criteria
      Set criteriaRange = wsSource.Range(“A2:A100”)

      ‘ Count the number of criteria
      lastRow = Application.WorksheetFunction.CountA(criteriaRange)

      ‘ Resize the criteria array based on the number of criteria
      ReDim criteriaArray(1 To lastRow)

      ‘ Populate the criteria array
      For i = 1 To lastRow
      criteriaArray(i) = criteriaRange.Cells(i, 1).Value
      Next i

      ‘ Clear any existing filters
      If wsTarget.AutoFilterMode Then
      wsTarget.AutoFilterMode = False
      End If

      ‘ Apply the AutoFilter on the target worksheet
      With wsTarget.Range(“A1”).CurrentRegion ‘ Assuming headers are in row 1
      .AutoFilter Field:=1, Criteria1:=criteriaArray, Operator:=xlFilterValues
      End With

      End Sub
      “`

      ### Explanation
      1. **Setting Worksheet References**:
      – `wsSource` is the worksheet containing the criteria (e.g., “SourceSheetName”).
      – `wsTarget` is the worksheet where you want to apply the AutoFilter (e.g., “TargetSheetName”).

      2. **Defining the Criteria Range**:
      – `criteriaRange` is the range in the source worksheet that contains your filter criteria (e.g., “A2:A100”).

      3. **Reading Criteria into an Array**:
      – `lastRow` determines the number of non-empty cells in the criteria range.
      – `criteriaArray` is populated with the values from the criteria range.

      4. **Clearing Existing Filters**:
      – Checks if there is any existing filter on the target worksheet and clears it if necessary.

      5. **Applying the AutoFilter**:
      – The `AutoFilter` method is applied to the current region starting from cell “A1” in the target worksheet, filtering column 1 based on the criteria in `criteriaArray`.

      ### Note
      – **Adjust Sheet Names and Ranges**: Ensure you replace `”SourceSheetName”` and `”TargetSheetName”` with the actual names of your worksheets.
      – **Header Row**: The code assumes headers are in row 1 of the target worksheet. Adjust the range if headers are in a different row.

      By following this approach, you can apply AutoFilter to a single column in your target worksheet using multiple criteria specified in a different worksheet.

Leave a Reply

Your email address will not be published. Required fields are marked *