VBA Advanced Filter

What Is VBA Advanced Filter?

With the VBA Advanced Filter feature, users can dynamically filter data using VBA. It offers greater customization and versatility than the built-in Excel filtering tool. Users can apply intricate filtering criteria, filter data across several columns, and copy the filtered results to another area of the same worksheet or an entirely new worksheet or workbook with VBA Advanced Filter.

Let us look at an example. Here, we have student data in the “Database” worksheet that contains Student Names and their marks in several subjects.

On the “Criteria” worksheet, there are fields provided for entering our filtering conditions. For instance, if we wish to filter only for the names “Sarah” and “John,” we enter these names in cells B2 and B3.

In this example, we have a VBA subroutine named “AdvancedFilterExample,” designed to demonstrate the application of advanced filtering. With defined cell ranges, these ranges are specified to match the criteria and data sheets, respectively.

After the ranges are defined, the subroutine uses the criteria range as the filter criteria and runs the AdvancedFilter function on the database range. The function filters the database in situ by applying the given criteria directly to the original data range by using “xlFilterInPlace” with the action argument.

Syntax

The syntax for applying an advanced filter in VBA is:

Range.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=CriteriaRange, CopyToRange:=CopyToRange, Unique:=Unique

Where:

1. Range is the data range that you want to filter.
2. Action specifies the action to be taken after filtering. It can be xlFilterCopy to copy filtered data or xlFilterInPlace to filter data in the same location.
3. CriteriaRange is the range that contains the criteria for filtering.
4. CopyToRange is the range where you want to copy the filtered results.
5. Unique is a Boolean value indicating whether to filter unique records only.

How to Create VBA Advanced Filter in Excel?

Examples

Example #1 – Advanced Filtering with Multiple Criteria

Suppose we have a dataset in “Sheet1” with data in columns A to C. We want to filter based on criteria provided in cells E1 to F2, copying the filtered results to cell H1. By using a VBA advanced filter with multiple criteria, we can achieve this.

Step 1: In the new module, we start by creating a subroutine and then declare the following variables.

Here, four variables are declared: three Range objects (rngData, rngCriteria, rngCopyTo) and one Worksheet object (ws).

1. rngData will reference the range containing the data you want to filter.
2. rngCriteria will reference the range containing the filter criteria.
3. rngCopyTo will reference the starting cell where the filtered data will be copied.
4. ‘ws’ will reference the worksheet containing your data and criteria.

Step 2: In this step, we will set the “ws” variable to reference Sheet3 of the workbook. This is where your data and criteria ranges are located.

Step 3: In this step, we declare the following ranges:

1. rngData is set to the range A1:C10, where your dataset is located.
2. rngCriteria is set to the range E1:F2, which contains the criteria for filtering the data.
3. rngCopyTo is set to cell H1, where the filtered results will be copied.

Step 4: Before applying the filter and copying new data, we will use “Clear” to clear any existing data in the area where the filtered results will be placed.

“CurrentRegion” is a property that returns a range bounded by any combination of blank rows and blank columns. It ensures that the destination range is ready to receive new data without overlapping or mixing with old data.

Step 5: Finally, we apply the VBA advanced filter property.

1. Action:=xlFilterCopy specifies that the filtered data should be copied to another location (as opposed to filtering in place).
2. CriteriaRange:=rngCriteria specifies the range that contains the criteria for filtering.
3. CopyToRange:=rngCopyTo specifies the range where the filtered data should be copied.
4. Unique:=False indicates that duplicate rows can be included in the filtered results. If set to True, only unique rows that meet the criteria would be copied.

Step 6: Now, save the macro and run the code. Excel will filter the data in the range A1:C10 on Sheet 3 based on the criteria specified in the range E1:F2.

The filtered results will then be copied to the area starting at cell H1 on the same sheet.

Here is the full code:

Sub AdvancedFilterMultipleCriteria()

    Dim rngData As Range

    Dim rngCriteria As Range

    Dim rngCopyTo As Range

        Dim ws As Worksheet

    Set ws = Sheets(“Sheet1”)

        Set rngData = ws.Range(“A1:C10”) ‘ Data range

        Set rngCriteria = ws.Range(“E1:F2”) ‘ Criteria range

        Set rngCopyTo = ws.Range(“H1”) ‘ Destination range

        rngCopyTo.CurrentRegion.Clear

        rngData.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rngCriteria, CopyToRange:=rngCopyTo, Unique:=False

End Sub

Example #2 – Advanced Filtering with Wildcards

In this example, we will see how to use VBA Advanced filters with wildcards to filter out the required data.

Step 1: Similar to the previous example, we start by creating a subroutine and then declare the variables.

In this example, we declare three variables, dataRange, criteriaRange, and copyToRange, each of type “Range”.

These variables will later be used to reference specific areas in the Excel worksheet: the range containing the data to be filtered, the range containing the filter criteria, and the range where the filtered results will be copied, respectively.

Step 2: In this step, we define the data range. The “Set” keyword is used to assign an object reference to a variable.

Here, it assigns the range A1:B50 on Sheet1 of the current workbook (ThisWorkbook) to the “dataRange” variable.

Step 3: This line of code assigns the range E1:E2 on Sheet1 to the “criteriaRange” variable. This range is where you’ve specified your filtering criteria.

Step 4: This line sets the top-left cell (H1 in this case) of the destination range on Sheet1, where the filtered results will be copied to, assigning it to the copyToRange variable.

Step 5: Next, we apply the VBA Advanced Filter, similar to the previous example.

Step 6: Now, when you run this code, Excel filters the data in the specified “dataRange” according to the criteria set in criteriaRange. Since our criteria is *Sr, it looks for any Job Title in the data range that starts with “Sr.”

The filtered results are then copied to the location starting at copyToRange, which is H1 in this example.

Here is the full code:

Sub UseAdvancedFilterWithWildcard()

    Dim dataRange As Range

    Dim criteriaRange As Range

    Dim copyToRange As Range

    Set dataRange = ThisWorkbook.Sheets(“Sheet1”).Range(“A1:B50”)

    Set criteriaRange = ThisWorkbook.Sheets(“Sheet1”).Range(“E1:E2”)

    Set copyToRange = ThisWorkbook.Sheets(“Sheet1”).Range(“H1”)

    dataRange.AdvancedFilter Action:=xlFilterCopy, criteriaRange:=criteriaRange, copyToRange:=copyToRange, Unique:=False

End Sub

Example #3 – Advanced Filtering Unique Values

In this example, we will see how to use the VBA advanced filters to filter out only the unique data and copy it into another cell.

Step 1: First, the variables are declared for the data range and the copy destination, similar to previous examples.

Step 2: Next, we set specific ranges to rngData (cells A1 to A10) and rngCopyTo (cell C1 in Sheet5).

Step 3: In this step, we apply the VBA Advanced Filter method. It filters unique values from rngData and copies them to rngCopyTo (Sheet5). The Unique parameter is set to True.

Step 4: When you run the code, it filters unique values from rngData and copies them to rngCopyTo in Sheet1.

Here is the full code:

Sub AdvancedFilterUniqueValues()

    Dim rngData As Range

    Dim rngCopyTo As Range

    Set rngData = Sheet5.Range(“A1:A10”) ‘ Data range

    Set rngCopyTo = Sheet5.Range(“C1”) ‘ Destination range

    rngData.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=rngCopyTo, Unique:=True

End Sub

Example #4 – Combining AND Criteria and OR Criteria

In this example, we will see how to use the VBA Advanced filter by combining with “And” and “Or” criteria.

Step 1: In this step, we declare variables to store ranges and worksheets. rngData, rngCriteria, and rngCopyTo will hold references to the data range, criteria range, and destination range, respectively.

“ws” will store a reference to the worksheet where the filtering will be applied.

Step 2: Here, we set the worksheet reference (ws) to the worksheet named “Fruit Data” using the Sheets collection and the sheet name as a string.

Step 3: We define the data range (rngData) by specifying the range A1:C10 on the worksheet referred to by ‘ws.’

Step 4: Here, we define the criteria range (rngCriteria) by specifying the range E1:F3 on the same worksheet. This range contains the criteria for filtering, including AND and OR criteria.

Step 5: We define the destination range (rngCopyTo) where the filtered results will be copied. In this case, it’s cell H1 on the same worksheet.

Step 6: Before applying the filter, any previous filter results in the destination range are cleared to ensure a clean slate for the new results.

Step 7: In this step, we use the VBA Advanced Filter method to apply the filter based on the criteria specified in rngCriteria.

The filtered results are then copied to the destination range specified in rngCopyTo.

Step 8: Here, we use a message box in VBA to display the user that the filter has been applied and to check the results in the specified destination range.

Step 9: Now, save the macro. When the code is run, it performs the steps outlined above, filtering the data based on the criteria specified and copying the filtered results to the destination range.

The user is then prompted to check the results in the specified cell.

Here is the full code:

Sub AdvancedFilterANDORCriteria()

    Dim rngData As Range

    Dim rngCriteria As Range

    Dim rngCopyTo As Range

    Dim ws As Worksheet

    Set ws = Sheets(“Fruit Data”)

   Set rngData = ws.Range(“A1:C20”) ‘ Data range

    Set rngCriteria = ws.Range(“E1:F4”) ‘ Criteria range with AND and OR criteria

    Set rngCopyTo = ws.Range(“H1”) ‘ Destination range

    rngCopyTo.CurrentRegion.Clear

    rngData.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=rngCriteria, CopyToRange:=rngCopyTo, Unique:=False

    MsgBox “Filter applied. Check the results in cell ” & rngCopyTo.Address

End Sub

Important Things To Note

1. Ensure that the data range, criteria range, and copy-to range are correctly specified and have the appropriate dimensions.
2. Wildcards in excel (*) can be used in criteria to represent any number of characters.
3. Advanced filtering can be applied to tables as well as regular ranges.
4. We can utilize the VBA Advanced Filter dynamic criteria range to adjust filtering criteria ranges based on changing data requirements adaptively.
5. We can use the VBA Advanced Filter multiple columns capability to efficiently filter data across multiple columns simultaneously.

Frequently Asked Questions (FAQs)

Download Template

This article must help us understand the VBA Advanced Filter formula and examples. You can download the template here to use it instantly.

Recommended Articles

Guide to VBA Advanced Filter in Excel. Here we explain how to create & use advance VBA filter, along with examples & downloadable excel template. You can learn more from the following articles –

• VBA AutoFilter
• VBA String Comparison
• VBA Pivot Table