Table of Contents
VBA can be used to filter a column in an Excel worksheet. This can be done by using a For Each loop to loop through each cell in the column and then using the If statement to check if the cell contains the desired value. The value can then be copied to a new worksheet. This process can be repeated until all desired values are filtered. Examples of this process can be found online.
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 .