VBA – Pivot Table Filter

Written by

Mel Jenkins

Reviewed by

Steve Rynearson

Last updated on April 4, 2022

This tutorial will demonstrate how to use the Pivot Table Filter in VBA.

Pivot tables are an exceptionally powerful data tool of Excel. Pivot tables enable us to analyze and interpret large amounts of data by grouping and summarizing fields and rows. We can apply filters to our pivot tables to enable us to quickly see the data that is relevant to us.

Firstly, we need to create a Pivot table for our data.  (Click Here for our VBA Pivot Table Guide)

VBA Filter Pivot

Creating a Filter Based on a Cell Value

In this example we will filter a Pivot Table based on a cell value.

In an empty cell to the right of the Pivot table, create a cell to hold the filter, and then type the data into the cell that you wish to filter the Pivot table on.

VBA Filter Page Filter

 

Create the following VBA Macro:

Sub FilterPageValue()
   Dim pvFld As PivotField
   Dim strFilter As String
   Set pvFld = ActiveSheet.PivotTables("PivotTable1").PivotFields("Supplier")
   strFilter = ActiveWorkbook.Sheets("Sheet1").Range("M4").Value
   pvFld.CurrentPage = strFilter
End Sub

Run the macro to apply the filter.

VBA Filter Page Filtered

To clear the filter, create the following macro:

Sub ClearFilter()
   Dim pTbl As PivotTable
   Set pTbl = ActiveSheet.PivotTables("PivotTable1")
   pTbl.ClearAllFilters
End Sub

The filter will then be removed.

We can then amend the filter criteria to filter on a row in the Pivot table rather than the Current Page.

VBA Filter CellValue

 

Typing the following macro will then enable us to filter on the Row (note that the Pivot Field to filter on is now the Oper rather than the Supplier).

Sub FilterRowValue()
   Dim pvFld As PivotField
   Dim strFilter As String
   Set pvFld = ActiveSheet.PivotTables("PivotTable1").PivotFields("Oper")
   strFilter = ActiveWorkbook.Sheets("Sheet1").Range("M4").Value
   pvFld.PivotFilters.Add2 xlCaptionEquals, , strFilter
End Sub

Run the macro to apply the filter.

VBA Filter RowFilter

Using Multiple Criteria in a Pivot Filter

We can add to the Row value filter above by adding additional criteria.

VBA Filter MultipleRow

However, as the standard filter hides the rows that are not required, we need to loop through the criteria and show the ones that are requited, while hiding the ones that are not required. This is done by creating an Array variable and using a couple of Loops in the code.

Sub FilterMultipleRowItems()
  Dim vArray As Variant
  Dim i As Integer, j As Integer
  Dim pvFld As PivotField
  Set pvFld = ActiveSheet.PivotTables("PivotTable1").PivotFields("Oper")
  vArray = Range("M4:M5")
  pvFld.ClearAllFilters

  With pvFld
    For i = 1 To pvFld.PivotItems.Count
      j = 1
       Do While j <= UBound(vArray, 1) - LBound(vArray, 1) + 1
         If pvFld.PivotItems(i).Name = vArray(j, 1) Then
           pvFld.PivotItems(pvFld.PivotItems(i).Name).Visible = True
           Exit Do
        Else
          pvFld.PivotItems(pvFld.PivotItems(i).Name).Visible = False
        End If
        j = j + 1
      Loop
    Next i
  End With
End Sub

Creating a Filter Based on a Variable

We can use the same concepts to create filters based on variables in our code rather than the value in a  cell. This time, the filter variable (strFilter) is populated in the code itself (eg: Hard-coded into the macro).

Sub FilterTextValue()
   Dim pvFld As PivotField
   Dim strFilter As String
   Set pvFld = ActiveSheet.PivotTables("PivotTable1").PivotFields("Supplier")
   strFilter = "THOMAS S"
   pvFld.CurrentPage = strFilter
End Sub

 

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users! vba save as


Learn More!
vba-free-addin

VBA Code Examples Add-in

Easily access all of the code examples found on our site.

Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.

(No installation required!)

Free Download

Return to VBA Code Examples