How To Filter In Excel Using Vba

Filtering in Excel using VBA

Filtering data in Excel using VBA (Visual Basic for Applications) allows you to automate the process of sorting and displaying specific information based on certain criteria. This can be useful when dealing with large datasets or when you need to perform repetitive filtering tasks.

Filtering Data based on a Single Criterion

To filter data based on a single criterion, you can use the “AutoFilter” method in VBA. Here’s an example:

    Sub FilterData()
        Dim ws As Worksheet
        Set ws = ThisWorkbook.Worksheets("Sheet1") ' Replace "Sheet1" with your sheet name
        
        ws.Range("A1:D1").AutoFilter Field:=1, Criteria1:="Apples" ' Replace "A1:D1" with your range and "Apples" with your criterion
    End Sub
  

In this example, the code filters the data in columns A to D (range A1:D1) based on the criterion “Apples” in the first column (Field:=1). Adjust the range and criteria according to your specific needs.

Filtering Data based on Multiple Criteria

If you need to filter data based on multiple criteria, you can use the “AdvancedFilter” method in VBA. Here’s an example:

    Sub FilterData()
        Dim ws As Worksheet
        Set ws = ThisWorkbook.Worksheets("Sheet1") ' Replace "Sheet1" with your sheet name
        
        Dim criteriaRange As Range
        Set criteriaRange = ws.Range("A1:B2") ' Replace "A1:B2" with your range of criteria
        
        Dim dataRange As Range
        Set dataRange = ws.Range("A4:D10") ' Replace "A4:D10" with your data range
        
        dataRange.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=criteriaRange
    End Sub
  

In this example, the code filters the data in the range A4:D10 based on the criteria defined in the range A1:B2. Adjust the ranges according to your specific needs.

Clearing Filters

To clear the filters applied to a worksheet, you can use the “ShowAllData” method in VBA. Here’s an example:

    Sub ClearFilters()
        Dim ws As Worksheet
        Set ws = ThisWorkbook.Worksheets("Sheet1") ' Replace "Sheet1" with your sheet name
        
        ws.ShowAllData
    End Sub
  

This code clears all filters applied to the worksheet specified. Adjust the sheet name accordingly.