How To Filter In Excel Vba

How to Filter in Excel VBA

Filtering data in Excel VBA allows you to display only specific records that meet certain criteria. You can use various filtering methods and techniques depending on your needs. In this guide, we will explain how to filter in Excel VBA with detailed examples.

1. AutoFilter Method

The AutoFilter method is one of the most commonly used filtering methods in Excel VBA. It allows you to filter a range of cells based on specific criteria. Here’s an example:


Sub FilterData()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    With ws.Range("A1:E10")
        .AutoFilter Field:=2, Criteria1:="Apple"
    End With
End Sub

This code filters the range A1:E10 in “Sheet1” based on the criteria that the value in the second column (Field:=2) should be “Apple”. The filtered results will be displayed on the worksheet.

2. Advanced Filter

The Advanced Filter feature in Excel VBA allows you to specify complex criteria and filter data based on multiple conditions. Here’s an example:


Sub AdvancedFilter()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    With ws.Range("A1:E10")
        .AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=ws.Range("G1:H2")
    End With
End Sub

This code applies an advanced filter to the range A1:E10 in “Sheet1” using the criteria specified in the range G1:H2. Make sure to set up the criteria range properly before running this code.

3. Filter Function

The Filter function in Excel VBA allows you to filter an array or a range of data based on specific criteria. Here’s an example:


Sub FilterFunction()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    Dim data As Variant
    data = ws.Range("A1:E10").Value
    
    Dim filteredData As Variant
    filteredData = Filter(data, "Apple", True, vbTextCompare)
    
    ws.Range("A1:E10").Value = filteredData
End Sub

This code filters the range A1:E10 in “Sheet1” by searching for the value “Apple” in the data. The filtered results are stored in the filteredData array and then pasted back into the same range.

4. Advanced Filter with Criteria Variables

You can also use variables to specify the criteria for advanced filtering in Excel VBA. Here’s an example:


Sub AdvancedFilterWithVariables()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    Dim criteria1 As String
    criteria1 = "Apple"
    
    Dim criteria2 As String
    criteria2 = "Red"
    
    With ws.Range("A1:E10")
        .AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=ws.Range("G1:H2"), Operator:=xlAnd, Criteria1:=criteria1, Criteria2:=criteria2
    End With
End Sub

This code applies an advanced filter to the range A1:E10 in “Sheet1” using the criteria specified in the range G1:H2 and the criteria stored in the variables criteria1 and criteria2. The Operator parameter (xlAnd in this case) specifies that both criteria should be met.

These are just a few examples of how to filter in Excel VBA. Depending on your requirements, you may need to use other filtering techniques or methods available in VBA. Experiment and explore different options to find the best solution for your specific needs.

How To Filter In Excel Vba