How to Filter a Column Using VBA (With Examples)


You can use the following methods to filter by a column in Excel using VBA:

Method 1: Filter Based on One Column Value

Sub FilterRows()
ActiveSheet.Range("A1:C11").AutoFilter field:=1, Criteria1:=Range("F2").Value
End Sub

This particular macro will filter the range A1:C11 to only display rows where the first column is equal to the value in cell F2.

Method 2: Filter Based on Multiple Column Values

Sub FilterRows()
ActiveSheet.Range("A1:C11").AutoFilter field:=1, Criteria1:=Range("F2").Value, _
                                 Operator:=xlOr, Criteria2:=Range("F3").Value
End Sub

This particular macro will filter the range A1:C11 to only display rows where the first column is equal to the value in cell F2 or F3.

Method 3: Clear Filters

Sub ClearFilters()
ActiveSheet.AutoFilterMode = False
End Sub

This particular macro will clear all filters from the current sheet.

The following examples show how to use each of these methods in practice.

Example 1: Filter Based on One Column Value

Suppose we have a dataset that contains information about various basketball players and we would like to filter the dataset to only contain rows where the value in the Team column is equal to A:

We can create the following macro to do so:

Sub FilterRows()
ActiveSheet.Range("A1:C11").AutoFilter field:=1, Criteria1:=Range("F2").Value
End Sub

When we run this macro, the dataset will automatically be filtered to only contain rows where the value in the Team column is equal to A:

VBA filter column based on cell value

Example 2: Filter Based on Multiple Column Values

Suppose we would like to filter the dataset to only contain rows where the value in the Team column is equal to A or C:

We can create the following macro to do so:

Sub FilterRows()
ActiveSheet.Range("A1:C11").AutoFilter field:=1, Criteria1:=Range("F2").Value, _
                                 Operator:=xlOr, Criteria2:=Range("F3").Value
End Sub

When we run this macro, the dataset is automatically filtered to only show rows where the value in the Team column is equal to A or C.

VBA filter column based on multiple criteria

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 Refresh Pivot Tables
VBA: How to Filter Pivot Tables
VBA: How to Remove Duplicate Values

Leave a Reply

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