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