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:
- Select any cell in your data table
- Go to Insert > PivotTable on the ribbon
- Confirm the Table/Range selected and choose where to place the pivot table
- 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:
- 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)
- 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:
- In the VB Editor, go to Insert > Module
- 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:
- Place your cursor inside the macro code
- 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:
- Create your pivot table
- Set up the pivot field layout
- Open the VBA editor and insert a module
- Write a macro using
PivotFields
andVisible
orPivotFilters
to specify the filter criteria - Run the macro to filter the pivot table
Here is a summary of key points:
Concept | Description |
---|---|
Pivot Table | Tool to summarize, analyze, filter, and present data in a flexible tabular format |
VBA Macro | Automated script to execute Excel commands, including filtering pivot tables |
PivotFields | Object representing a field in a pivot table, used to access items for filtering |
Visible | Property to show or hide individual pivot items when filtering |
PivotFilters | Collection of filters applied to a pivot field, can filter on various criteria |
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
What are the steps to create a pivot table in Excel?
- Select any cell in your data table.
- Go to Insert > PivotTable on the ribbon.
- Confirm the Table/Range selected and choose where to place the pivot table.
- Click OK to insert a blank pivot table.
How do you access the VBA editor in Excel?
What VBA code is used to filter a pivot table?
PivotFields
object to reference the fields you want to filter and the Visible
property to specify which items to show or hide. For example:
With pt.PivotFields("Region")
.PivotItems("East").Visible = True
.PivotItems("West").Visible = True
.PivotItems("Central").Visible = False
.PivotItems("South").Visible = False
End With
Can you filter a pivot table based on criteria using VBA?
PivotFilters
collection of the pivot field to add criteria-based filters, such as equals, greater than, less than, between, or top 10. For example, to filter for sales amounts greater than $20,000:
With pt.PivotFields("Sales Amount")
.ClearAllFilters
.PivotFilters.Add Type:=xlValueIsGreaterThan, Value1:="20000"
End With
How do you run a VBA macro to filter a pivot table?
- Place your cursor inside the macro code in the VBA editor.
- Go to Run > Run Sub/UserForm (or press F5).
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.