This tutorial explains how to filter data using VBA in Excel.
You can download the following dataset to practice.
Sub BasicAutoFilter() Range("A1:D6").AutoFilter Field:=2, Criteria1:="John" End Sub
- Range : The range of cells to which the filter will be applied.
- Field : The column number to which the filter will be applied.
- Criteria1 : The filter condition (like filter "John").
The following code can be used to filter age greater than "30" in column B of dataset.
Sub greaterthan() Dim ws As Worksheet Dim lastRow As Long Set ws = ThisWorkbook.Sheets("Sheet1") lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row If ws.AutoFilterMode Then ws.AutoFilterMode = False End If ws.Range("A1:D" & lastRow).AutoFilter Field:=2, Criteria1:=">30" End Sub
Press Run or F5 to run the above macro.
- Less Than Filter :
Criteria1:="<30"
represent value less than 30. - Equal to Filter :
Criteria1:="=30"
represent value equal to 30. - Greater Than or Equal to Filter :
Criteria1:=">=30"
represent value greater than or equal to 30. - Less Than or Equal to Filter :
Criteria1:="<=30"
represent value less than or equal to 30. - Between Two Values Filter :
Criteria1:=">28", Criteria2:="<32"
represent value greater than '28' but less than '32'.
This section explains how to filter multiple columns. The following code can be used to filter rows where the Name column (Column A) shows 'John' and the City column (Column C) shows 'New York':
Sub ApplyANDCondition() Dim ws As Worksheet Dim lastRow As Long Set ws = ThisWorkbook.Sheets("Sheet1") lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row If ws.AutoFilterMode Then ws.AutoFilterMode = False End If ws.Range("A1:D" & lastRow).AutoFilter Field:=1, Criteria1:="John" ws.Range("A1:D" & lastRow).AutoFilter Field:=3, Criteria1:="New York" End Sub
Field:=1 refers to first column. Field:=3 refers to third column.
In OR condition
, either one of the criteria needs to be true. Operator:=xlOr
is used to apply OR condition. The following macro can be used to filter the rows in which name column shows either "John" or "Mike" :
ws.Range("A1:D" & lastRow).AutoFilter Field:=1, Criteria1:="John", Operator:=xlOr, Criteria2:="Mike"
The following code can be used to filter the rows where the 'Name' column shows entries that begin with the letter 'J' :
Sub FilterContainsText() Dim ws As Worksheet Dim lastRow As Long Set ws = ThisWorkbook.Sheets("Sheet1") lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row If ws.AutoFilterMode Then ws.AutoFilterMode = False End If ws.Range("A1:D" & lastRow).AutoFilter Field:=1, Criteria1:="J*" End Sub
Press Run or F5 to run the above macro.
- Ends with specific text :
Criteria1:="*xyz"
represents text that ends with 'xyz'. - Contains specific text :
Criteria1:="*xyz*"
represents text that contains 'xyz'. - Does not contain specific text :
Criteria1:="<>*xyz*"
represents text that does not contain 'xyz'.
The following code can be used to filter the rows where the Name column shows 'John' and copies them from Sheet1 to Sheet2 :
Sub FilterAndCopyData() Dim wsSource As Worksheet Dim wsDest As Worksheet Dim lastRow As Long Dim filteredRange As Range Set wsSource = ThisWorkbook.Sheets("Sheet1") Set wsDest = ThisWorkbook.Sheets("Sheet2") lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row wsSource.Range("A1:D" & lastRow).AutoFilter Field:=1, Criteria1:="John" On Error Resume Next Set filteredRange = wsSource.Range("A1:D" & lastRow).SpecialCells(xlCellTypeVisible) On Error GoTo 0 If Not filteredRange Is Nothing Then filteredRange.Copy Destination:=wsDest.Range("A1") End If wsSource.AutoFilterMode = False End Sub
Press Run or F5 to run the above macro.
Share Share Tweet