Excel VBA Autofilter: Find What You Need to Know

Excel’s Visual Basic for Applications (VBA) is a powerful tool for automating repetitive tasks.

One of its versatile features is the Autofilter. This functionality enables users to filter large sets of data based on specific criteria.

By writing VBA macros, one can create custom filters to display only the rows that meet certain conditions. This enhances the user’s ability to manage and review data efficiently.

The Autofilter method in VBA is applied to a range of cells and can be customized with various criteria, allowing for intricate filtering operations that can be activated with a simple macro.

Automation through VBA not only saves time but also reduces the potential for human error. This makes it an invaluable asset for Excel users who regularly work with extensive datasets and require dynamic methods for data manipulation.

Macros that utilize the Autofilter method can be designed to accommodate a wide range of data management tasks—from basic filtering to more complex, multi-criterion scenarios.

This flexibility makes VBA an essential skill for those looking to enhance their capabilities in Excel.

By employing the Autofilter in VBA, users can execute targeted data operations, making it an integral part of any data-driven environment.

Understanding Autofilter in Excel

Excel’s Autofilter functionality provides a straightforward way to display only the information that meets certain criteria from a large dataset.

Basics of Autofilter

Autofilter is a feature in Excel that allows users to easily filter and sort through data within a range of cells. It adds a drop-down menu to the header row of a table or a range, from which users can choose to display only rows that contain certain values. They can efficiently work with large amounts of data by displaying only the information that is relevant to their immediate needs.

The Autofilter Method

The Autofilter method is part of Excel’s VBA (Visual Basic for Applications) programming language. It enables programmatic control over the filtering functionality within Excel sheets.

For example, a user can apply the Autofilter method on a specified range object to filter the data based on criteria or to clear the filter, thereby showing all the data again.

Autofilter Vs. Advanced Filter

While both Autofilter and Advanced Filter serve the purpose of filtering data, they do so at varying levels of complexity and control.

Autofilter is better suited for quick and simple filters directly on the dataset. In contrast, the Advanced Filter allows for more complex criteria and can be used to copy the filtered data to another location.

Advanced Filter can handle complex filtering tasks that may involve multiple conditions for a single column or different conditions across multiple columns.

Implementing Autofilter with VBA

Visual Basic for Applications (VBA) enhances Excel with automation possibilities, allowing users to apply Autofilter programmatically to ranges and tables.

Writing Your First Autofilter VBA Code

To begin employing Autofilter in VBA, one initiates by accessing the Developer tab in Excel to open the Visual Basic Editor.

The simplest form of an Autofilter VBA code involves specifying the exact range to be filtered and the criteria. Here is a basic example:

Sub ApplyAutoFilter()
    Range("A1:D1").AutoFilter Field:=1, Criteria1:="Criteria"

This macro applies an Autofilter to the data in columns A through D, filtering the first field (column A), based on the defined criteria.

Using the Range.Autofilter Method

The Range.Autofilter method in VBA is versatile, providing multiple parameters for detailed filtering. The method’s structure is as follows:

A typical usage would be to filter a range based on multiple criteria:

Sub ApplyMultiCriteriaFilter()
    With Range("A1:D100")
        .AutoFilter Field:=2, Criteria1:=">100", Operator:=xlAnd, Criteria2:="<200"
    End With

This code filters the second column for values greater than 100 and less than 200.

Working with ListObjects

When dealing with tables in Excel, referenced as ListObjects in VBA, one can use Autofilter directly on the ListObject. This leverages structured referencing, which makes the VBA code easier to read and maintain.

Here’s how to apply autofilter on a table named “SalesData”:

Sub FilterTable()
    Dim tbl As ListObject
    Set tbl = ThisWorkbook.Worksheets("Sheet1").ListObjects("SalesData")
    tbl.Range.AutoFilter Field:=1, Criteria1:=">=1000", Operator:=xlFilterValues

Filtering Data Using Autofilter

Autofilter in Excel VBA enables users to display only the rows that meet certain criteria, making it an essential tool for managing data sets.

Filtering Text, Numbers, and Dates

Users can easily filter text, numbers, and dates using the Autofilter feature in Excel.

To filter text, one selects the relevant column and specifies the text criteria. For example:

Range("A1").AutoFilter Field:=1, Criteria1:="Specific Text"

For numbers and dates, the process is similar, with the addition of relational operators to define the range.

One could filter for numbers greater than a specific value:

Range("B1").AutoFilter Field:=2, Criteria1:=">10"

Or to filter dates before a certain day:

Range("C1").AutoFilter Field:=3, Criteria1:="<=" & DateSerial(2024, 7, 10)

Applying Multiple Criteria

Excel VBA’s Autofilter can also handle multiple criteria.

To apply two criteria within the same column, use the Criteria1 and Criteria2 along with an operator.

For instance, to display rows with numbers between 10 and 20:

Range("A1").AutoFilter Field:=1, Criteria1:=">=10", Criteria2:="<=20", Operator:=xlAnd

For different columns, simply apply the filter to each field as needed:

With Range("A1:C1")
    .AutoFilter Field:=1, Criteria1:="Specific Text"
    .AutoFilter Field:=2, Criteria1:=">10"
    .AutoFilter Field:=3, Criteria1:="<=2024"

Using Wildcards for Complex Criteria

Complex criteria can be managed using wildcards. The asterisk * represents any series of characters, and the question mark ? represents any single character. This is particularly useful for text filters.

To filter cells that start with “Ex” and end with “l”:

Range("A1").AutoFilter Field:=1, Criteria1:="Ex*l"

When searching for a single character variation say, “apples” or “apples”:

Range("A1").AutoFilter Field:=1, Criteria1:="apple?"

Manipulating Data Post-Filtering

After applying the AutoFilter in Excel VBA to streamline data sets, various operations can be performed on the filtered data to organize and manipulate it according to the user’s needs.

Copying and Pasting Filtered Data

One may need to copy filtered data to a different location. They use Range.Copy method to transfer the data.

After filtering, only visible cells are affected by the Copy method. To paste the copied data, the Worksheet.Paste method or Range.PasteSpecial can be used, specifying the destination range.

For instance, to copy the filtered results from “Sheet1” to “Sheet2”:

With Sheets("Sheet1")
    .AutoFilterMode = False
    With .Range("A1:C100")
        .AutoFilter Field:=1, Criteria1:="Criteria"
        .SpecialCells(xlCellTypeVisible).Copy
    End With
End With

Sheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Moving Rows and Deleting Columns

Moving rows after filtering can rearrange a data set efficiently. Users utilize the cut and insert methods for moving.

They implement Range.Cut and Range.Insert to perform these tasks. Similarly, deleting specific columns in a filtered state means using the Range.Delete method on visible cells.

To delete all visible cells in column “C”:

With Sheets("Sheet1")
    .AutoFilterMode = False
    With .Range("C1:C100")
        .AutoFilter Field:=1, Criteria1:="Criteria"
        .SpecialCells(xlCellTypeVisible).Delete
    

Clearing and Reapplying Filters

When a user needs to start fresh with data analysis, they clear current filters using the AutoFilterMode property set to False.

For reapplication of filters, they set AutoFilter method with required criteria for the same or different fields.

Users often apply the Find method to locate specific data before re-filtering.

A process to clear and reapply a filter:

With Sheets("Sheet1")
    ' Clear existing filters
    .AutoFilterMode = False
    
    ' Reapply filter
    With .Range("A1:C100")
        .AutoFilter Field:=2, Criteria1:="<>"
    

Dean Portfolio Manager

Dean Graham is the founder and editor of 9to5flow.com, a website focused on productivity and work-life balance. Dean's career is in commercial banking where he has held various roles where he has encountered the everyday challenges faced by professionals. In 2022, Dean created 9to5flow.com to share practical advice and resources aimed at helping people achieve their goals while maintaining well-being. He hopes the site can provide readers with relatable insights and straightforward tips, as researching these topics has been a valuable exercise for his own career. Outside of the digital space, Dean enjoys the outdoors, college football, live music and being with his family. He finds happiness in continuous learning and helping others find a balanced approach to work and life.

See Full Bio