VBA : How to Filter Data

This tutorial explains how to filter data using VBA in Excel.

You can download the following dataset to practice.

VBA Code to Use Filter

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").

1. Greater Than Condition

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.

VBA : Using Greater Than Filter in Excel

Important Numeric Filters

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

2. Filter Multiple Columns

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.

OR Condition

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"

3. Filter Names Beginning with Specific Letter

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.

Important Text Filters

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

4. Filter and Copy Data to Another Sheet

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.

VBA : Filter and copy data to another sheet

About Author:
Yash Gaur

Yash is pursuing an MBA in Finance with a keen interest in analytics. He enjoys working with data and leveraging his research and analytical skills to generate valuable insights.

Let's Get Connected Email LinkedIn