How To Filter Excel Pivot Table Using VBA: Easy Guide

Are you looking to automate filtering of your Excel pivot tables using VBA (Visual Basic for Applications)? Pivot tables are a powerful way to summarize, analyze, and present large amounts of data in Excel. And by using VBA macros, you can take your pivot table automation to the next level, saving time and effort. In this article, we’ll walk through step-by-step how to filter an Excel pivot table using VBA code.

What is an Excel Pivot Table?

Before diving into the VBA code, let’s briefly review what pivot tables are. A pivot table is a tool in Excel that lets you summarize and analyze data in a flexible, interactive way. With a pivot table, you can:

• Summarize large amounts of data into a compact format
• Easily rearrange the layout to view the data in different ways
• Drill down into more detail
• Filter, sort, and group the summarized data

Pivot tables are created from a source data table and let you “pivot” the rows and columns to see different summaries and insights. They are very helpful for exploring large datasets and uncovering patterns.

Why Use VBA to Filter a Pivot Table?

While you can manually filter a pivot table using the built-in filters and slicers in the Excel interface, using VBA offers some key advantages:

• Automate repetitive filtering – If you need to apply the same filters to a pivot table on a regular basis, a VBA macro can automate this recurring task.
• Access advanced filtering – VBA provides more advanced and flexible filtering options beyond the basic filters available in the Excel interface.
• Integrate with other automation – Filtering a pivot table can be one step in a larger VBA macro that automates a multi-step workflow or report generation process.

So if you work with pivot tables frequently and want to streamline the filtering process, learning to filter using VBA is a valuable skill. Let’s look at how to do it.

Step-by-Step: Filter Pivot Table Using VBA

Here is the step-by-step process to filter an Excel pivot table using a VBA macro:

Step 1: Create Your Pivot Table

Start by creating the pivot table you want to filter. Select your source data table and insert a pivot table:

1. Select any cell in your data table
2. Go to Insert > PivotTable on the ribbon
3. Confirm the Table/Range selected and choose where to place the pivot table
4. Click OK to insert a blank pivot table

Step 2: Set Up the Pivot Table Fields

Arrange the fields in your pivot table how you want them. Drag fields into the Rows, Columns, and Values areas to set up your desired pivot table layout and calculations.

Step 3: Open the VBA Editor

To write your VBA macro, you’ll need to access the Visual Basic Editor:

1. Go to the Developer tab on the ribbon (if you don’t see the Developer tab, you may need to enable it in Excel Options)
2. Click the Visual Basic button to open the editor

Step 4: Insert a New Module

VBA macros are stored in modules. Insert a new module to hold your macro code:

1. In the VB Editor, go to Insert > Module
2. A new module sheet will appear for your macro

Step 5: Write the VBA Code to Filter

Now you’re ready to write the VBA code that will filter your pivot table. Use the PivotFields object to reference the fields you want to filter, and the Visible property to specify which items to show or hide.

Here’s an example macro that filters a pivot table to only show certain items for two different fields:

Sub FilterPivot()
    Dim pt As PivotTable
    Set pt = ActiveSheet.PivotTables("PivotTable1")

    'Filter "Region" field to only show "East" and "West" 
    With pt.PivotFields("Region")
        .PivotItems("East").Visible = True
        .PivotItems("West").Visible = True
        .PivotItems("Central").Visible = False
        .PivotItems("South").Visible = False
    End With

    'Filter "Category" field to only show "Furniture" and "Office Supplies"
    With pt.PivotFields("Category")  
        .PivotItems("Furniture").Visible = True
        .PivotItems("Office Supplies").Visible = True
        .PivotItems("Technology").Visible = False
    End With
End Sub

To break this down:

• The macro is named “FilterPivot” with Sub FilterPivot()
• It declares a PivotTable object variable pt and sets it to refer to a pivot table named “PivotTable1” on the active sheet
• It uses a With block to filter the “Region” field, setting “East” and “West” to visible and the other items to not visible
• It uses another With block to similarly filter the “Category” field to only show “Furniture” and “Office Supplies”

Step 6: Run the Macro

To execute the macro and filter your pivot table:

1. Place your cursor inside the macro code
2. Go to Run > Run Sub/UserForm (or press F5)

The macro will run and your pivot table will update to show only the items specified in the filter code.

Filtering Pivot Table Based on Criteria

In addition to filtering for explicit items, you can also write macros to filter a pivot table based on criteria, similar to how you would filter a regular table with formulas.

To do this, use the PivotFilters collection of the pivot field to add criteria like:

• Equals
• Greater than
• Less than
• Between
• Top 10

For example, here’s a macro that filters a pivot table based on sales amounts greater than $20,000:

Sub FilterPivotAmount()
    Dim pt As PivotTable
    Set pt = ActiveSheet.PivotTables("SalesPivot")

    With pt.PivotFields("Sales Amount")
        .ClearAllFilters
        .PivotFilters.Add Type:=xlValueIsGreaterThan, Value1:="20000" 
    End With
End Sub

This uses the PivotFilters.Add method to add a filter for values greater than 20,000 on the “Sales Amount” field. The ClearAllFilters method first removes any existing filters on that field.

Here’s another example that filters for the top 5 sales reps:

Sub FilterPivotTop5()
    Dim pt As PivotTable
    Set pt = ActiveSheet.PivotTables("SalesPivot")

    With pt.PivotFields("Sales Rep")
        .ClearAllFilters
        .PivotFilters.Add Type:=xlTopCount, Value1:=5, Operator:=xlDescending
    End With
End Sub 

This uses a top count filter type with a value of 5, specifying to sort descending to filter for the reps with the top 5 totals.

Summary

Filtering Excel pivot tables using VBA provides a powerful way to automate the filtering process, access advanced filtering functions, and integrate pivot table filtering into larger VBA-driven workflows.

To recap, the key steps to filter a pivot table with VBA are:

1. Create your pivot table
2. Set up the pivot field layout
3. Open the VBA editor and insert a module
4. Write a macro using PivotFields and Visible or PivotFilters to specify the filter criteria
5. Run the macro to filter the pivot table

Here is a summary of key points:

I hope this article has helped you understand how to leverage the power of VBA to filter your Excel pivot tables. With practice, you’ll be able to create increasingly sophisticated macros to analyze your data faster than ever.

People Also Ask

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.