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

i just replaced A B by the column i want to use.do you know what happened?

it works.

thank you man

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 ?

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.