How to Use Excel VBA to Filter Pivot Table

We create a pivot table to visualize our dataset more closely by comparing desired fields directly removing unnecessary fields. In the meantime, we also can deploy a filter in that pivot table to elevate our analysis to the next level. In this article, we are going to show how, in Excel, you can use VBA to deploy Filter operation in the pivot table. Below, a sample method is shown with an example.

Here, we can create a pivot table with a single criteria filter.

Excel VBA to Filter Pivot Table: 5 Suitable Examples

Here, we are going to present 5 Separate Examples of using the Excel VBA macro to filter value in the pivot table. Users need to understand the methods and use them accordingly. In order to avoid any kind of compatibility issue, please use the Microsoft Excel 365 edition.

1. Filter in Pivot Table Based on Single Criteria Using VBA

Using a single criteria filter is the most common way people use the FIlter in the Excel pivot table.

Before we apply the Filter to the Pivot table, we first need to understand how we can create a pivot table. Then, we can add a filter to it.
To create a pivot table, a sample code is given below.
The user first needs to open the VBA editor following the Developer tab >> Visual Basic.
Next in the “Microsoft Visual Basic for Applications” window click Insert >> Module.
After opening the editor, paste the below code to the editor. Then press the Run command.

Sub CreatePivotTableWithFilter()
Dim dataRange As Range
Set dataRange = ActiveSheet.Range("B4:F19")
Dim pivotDestination As Range
Set pivotDestination = ActiveSheet.Range("B22")
Dim pivotTable As pivotTable
Set pivotTable = ActiveSheet.PivotTables.Add(PivotCache:= _
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
dataRange), TableDestination:=pivotDestination, TableName:="PivotTable4")
With pivotTable
.PivotFields("Region").Orientation = xlRowField
.AddDataField .PivotFields("Sales"), "Total Sales", xlSum
.AddDataField .PivotFields("Quantity"), "Total Quantity", xlSum
End With
End Sub

After pressing Run, we will see that there is a pivot table in the worksheet, with field properties set in the code.
We can modify this code and add a filter to the pivot table.
For this enter the following code to the VBA editor.

Sub CreatePivotTableWithFilter()
Dim dataRange As Range
Set dataRange = ActiveSheet.Range("B4:F19")
Dim pivotDestination As Range
Set pivotDestination = ActiveSheet.Range("B23")
Dim pivotTable As pivotTable
Set pivotTable = ActiveSheet.PivotTables.Add(PivotCache:= _
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
dataRange), TableDestination:=pivotDestination, TableName:="PivotTable4")
With pivotTable
.PivotFields("Region").Orientation = xlRowField
.AddDataField .PivotFields("Sales"), "Total Sales", xlSum
.AddDataField .PivotFields("Quantity"), "Total Quantity", xlSum
.PivotFields("Product").Orientation = xlPageField
.PivotFields("Product").ClearAllFilters
.PivotFields("Product").CurrentPage = "Widget"
End With
End Sub

Now we can see that the pivot table has a Filter on top of the pivot table.
Switching the values on the pivot table will change the values in the pivot table.

2. Filter Pivot Table Based on Multiple Criteria Using VBA

In the previous method, we have seen how to create a Pivot table with a Filter. Now, in this method, we will discuss how we can create a pivot table that will have a filter with multiple-choice options. Meaning we can filter table values based on two or more separate criteria at the same time.

Sub Multiple_FILTER()
Dim pt As pivotTable
Dim PF_PRODUCT As PivotField
Set pt = ThisWorkbook.Worksheets("Data").PivotTables("Dataset")
Set PF_PRODUCT = ActiveSheet.PivotTables("Dataset").PivotFields("Product")
PF_PRODUCT.ClearAllFilters
PF_PRODUCT.EnableMultiplePageItems = True
PF_PRODUCT.PivotItems("Widget").Visible = False
PF_PRODUCT.PivotItems("Remote").Visible = False
Set PF_PRODUCT = Nothing
Set pt = Nothing
End Sub

3. Filter Based on Cell Value

We did filtering of the pivot table by using the macro in single and multiple criteria. Now, we can do our filtering based on a cell value.

First, we need to create a Pivot table with the Region in the row field and the Quantity and the Sales in the values field.
Now we want to filter the values according to the Product name.
The user first needs to open the VBA editor following the Developer tab >> Visual Basic.
Next in the “Microsoft Visual Basic for Applications” window click Insert >> Module.
After opening the editor, paste the below code to the editor. Then press the Run command.

Sub Pivot_Table_Filter_Based_on_Cell_Value()
Dim dataRange As range
Set dataRange = ActiveSheet.range("B4:F19")
Dim pivotDestination As range
Set pivotDestination = ActiveSheet.range("B23")
Dim pivotTable As pivotTable
Set pivotTable = ActiveSheet.PivotTables.Add(PivotCache:= _
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
dataRange), TableDestination:=pivotDestination, TableName:="PivotTable25")
With pivotTable
.PivotFields("Region").Orientation = xlRowField
.AddDataField .PivotFields("Sales"), "Total Sales", xlSum
.AddDataField .PivotFields("Quantity"), "Total Quantity", xlSum
.PivotFields("Product").Orientation = xlPageField
.PivotFields("Product").ClearAllFilters
.PivotFields("Product").CurrentPage = ActiveSheet.range("E22").Value
End With
End Sub

After pressing the Run command, we can see that the PivotTable is now created with the mentioned fields and filter button at the top.
The interesting part is that the filter button criteria depend on the cell value of the cell. The Filter button will choose the value to apply a filter on the pivot table same as the value in cell E22.
If we change the value in cell E22 to something else, the filter criteria change as well, and the value in the pivot table is updated accordingly.

4. Using Slicers to Filter Pivot Tables

You can add a slicer instead of using a slicer to filter down your data more efficiently and make the process more visually and aesthetically pleasing. We will present the below code, using which you can filter your data.

But first, you need to have a pivot table ready for applying the slicer.
The pivot table from the dataset is created and given below.

As you can see we have that Region in the Row field and then the Sum of Sales and Quantity in the values fields.
Now, we will add the slicer to the worksheet.
For this, you first need to open the VBA code editor from the Developer tab, as mentioned before.
Then in the code editor, enter the following code,

Sub AddSlicerToPivotTable()
Dim sl As slicer
Dim pt As pivotTable
Dim slic_cache As SlicerCache
Set pt = ThisWorkbook.Worksheets("Using Slicer").PivotTables("PivotTable2")
Set slic_cache = ThisWorkbook.SlicerCaches.Add2(pt, "Product", "ProductCache", XlSlicerCacheType.xlSlicer)
Set sl = sli_cache.Slicers.Add(slicerdestination:=Worksheets("Using Slicer"), Name:="slicer2")
End Sub

After entering this code, we can see that there is a slicer in the worksheet. And you can filter the value in the pivot table based on what value you choose on the slicer.
As you souffle through the values in the Slicer, the values in the Pivot table also change accordingly.

5. Creating Dynamic Filters with VBA

This Pivot table is a little bit different compared to the previous codes. Here, the filter criteria will depend on a specific cell value. If the cell value is updated, then the pivot table will update accordingly.

For this, we will present a code that will add a pivot table in the worksheet and add a filter to it. The filter of the pivot table will be dynamic and will depend on the cell value E22.
But first, the user needs to right-click on the sheet name panel and then click on the View Code.
Then in the code editor, paste the code given below.
Inside the module paste the code given below,

Private Sub Worksheet_Change(ByVal Target As range)
If Target.Address = "$E$22" Then
Dim dataRange As range
Set dataRange = Me.range("B4:F19")
Dim pivotDestination As range
Set pivotDestination = Me.range("B23")
Dim pivotTable As pivotTable
Dim ws As Worksheet
Dim pt As pivotTable
On Error Resume Next
Set ws = Worksheets("Dynamic Filter")
Set pt = ws.PivotTables("PivotTable16")
pt.TableRange2.Clear
Set pivotTable = Me.PivotTables.Add(PivotCache:= _
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
dataRange), TableDestination:=pivotDestination, TableName:="PivotTable16")
With pivotTable
.PivotFields("Region").Orientation = xlRowField
.AddDataField .PivotFields("Sales"), "Total Sales", xlSum
.AddDataField .PivotFields("Quantity"), "Total Quantity", xlSum
.PivotFields("Product").Orientation = xlPageField
.PivotFields("Product").ClearAllFilters
.PivotFields("Product").CurrentPage = Me.range("E22").Value
End With
End If

As this code is Private Sub, user do not need to run the code manually from the view tab.
When the user changes the value of cell E22, the code will execute automatically and show the pivot table in the worksheet.
Not only this, but the table will also filter the data based on the cell value in cell E22.

Clear Filters from the Pivot Table Using Excel VBA

All the code presented before is the code for adding a pivot table to the worksheet and then applying a filter to it. Those filters might be dynamic, single criteria, or multi-criteria. In this section, we will discuss how we can remove an existing filter from a worksheet pivot table.

First, we need to have a pivot table in the worksheet. We have a pivot table already in the worksheet named, and the filter is applied to the Product field.
And the filter is now applied for Model Boat and Widget. We need to remove this filter from this pivot table.

Sub Clear_Filter()
ActiveSheet.PivotTables("PivotTable3").PivotFields("Product").ClearAllFilters
End Sub

After that, we will see that the PivotTable3 is now free from any kind of filter and now shows all the data points in the dataset.

Things to Remember

Pivot Table Structure: Filters are applied to the entire pivot table, not just specific rows or columns. So, when you apply a filter, it affects the visibility of data across the entire table.

Filter Types: Excel provides various types of filters in pivot tables, including label filters, value filters, and manual filters. Each type allows you to filter data based on different criteria.

Field Selection: Choose the correct field to apply the filter. Pivot tables have row fields, column fields, report filter fields, and value fields. Ensure you select the appropriate field to filter the data you want.

Filtering Options: Depending on the type of data in the field, you can use different filtering options. For example, text fields offer options like contains, begins with, ends with, etc., while number fields provide options like equals, greater than, less than, etc.

Multiple Filters: You can apply multiple filters to a pivot table. Each filter narrows down the data further, helping you focus on specific subsets of information.

Updating Filters: If the underlying data in your pivot table changes, you might need to update the filters to reflect the latest information. Right-click on the pivot table and select “Refresh” to update the filters and the entire pivot table.

Clearing Filters: To remove filters and display the entire dataset, right-click on the pivot table, select “Clear Filter,” and choose the appropriate option to clear the filters.

Grouping Data: In addition to filters, you can also group data in a pivot table. Grouping allows you to combine and summarize data based on specific criteria, such as date ranges or custom categories.

Saving Filters: Excel does not save filters within the pivot table. If you save the workbook and reopen it later, the filters will be cleared. If you want to preserve specific filter settings, you can create a copy of the filtered pivot table or save the filtered data in a separate worksheet.

Filter Interactions: Filters in a pivot table can interact with each other. When you apply multiple filters, they can affect each other’s results. Be mindful of the filter order and how they impact the data visibility.

Frequently Asked Question

How can I show Maximum value of data in pivot table ?

Yes, you can show the maximum value of the Pivot table Filter following the below images.

First, you need to right-click on the Filter field value.

Then, from the context menu, click on the Field Settings.

Then, in the new window, choose Custom in the Subtotal and & Filters tab.
Right after this, choose the Max option in the list.
Click OK after this.

How do I select multiple items in a PivotTable filter VBA?

Steps:

Yes, you can select multiple items in the pivot table filter. The value you choose in the field is going to be placed on the worksheet. You just have to check the Select Multiple Items box. After checking the box, you can select multiple values in the worksheet.

Can you have two value filters in pivot table?

No, you can’t have multiple filter values in a single pivotable. You are restricted to only one filter in the pivot table. If you use one filter and then try to use another filter, it will remove the previous filter without any warning.

Download Practice Workbook

Download the following workbook to practice by yourself.

VBA Filter Pivot Table.xlsm

Conclusion

In this article, we have learned how in Excel, we used VBA macro to create a filter in the pivot table. And using various kinds of filter criteria and how we can implement them. We finally showed how we can clear the filters applied in the worksheet using a macro.

<< Go Back to Pivot Table Filter | Pivot Table in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!