How to Change a Pivot Table Filter in VBA

Written by linda banks
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Change a Pivot Table Filter in VBA
(Creatas Images/Creatas/Getty Images)

An Excel pivot table is a powerful solutions tool. A primary feature of a pivot table is its ability to filter data. This allows the user to limit the amount of data that he has to browse to get his answer. With just a little bit of Visual Basic for Applications, or VBA, programming, the user can build functionality that automatically changes the pivot table filter either in response to an update or as part of a standardised process.

Skill level:


  1. 1

    Create a data table in Excel. In row 1, column A, type the word "Color." In row 1, column B, type the word "Letter." In row 1, column C, type the word "Number." For several rows in column A, type in several colours, such as red or green. For Columns B and C, type in random letters and numbers to create the data table.

  2. 2

    Insert a pivot table based on the data. Filter on the Color column by deselecting "Green."

  3. 3

    Open the Visual Basic editor. Do this by pressing "ALT"+"F11" or selecting "Tools" from the top menu bar, then select "Macros" -> "Visual Basic Editor."

  4. 4

    Click on "Insert" in the top menu bar and select the "Module" option.

  5. 5

    Type the following Visual Basic code:

    Public Sub FilterPivotTable()

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Color")

            .PivotItems("Green").Visible = True

    End With

    End Sub

  6. 6

    Run through the code by pressing "F5" or walk through the code line-by-line by pressing "F8." The pivot table will now show both the Red and Green columns. To turn off the filter, change the word "True" to "False."

Tips and warnings

  • It is almost always easiest to record keystrokes when creating a new Visual Basic module and then modifying what has been recorded. To record your keystrokes, select "Tools" from the top menu bar, then "Macro" and "Record New Macro." Filter and unfilter the pivot table a few times before stopping the recording. Open the Visual Basic editor and review what has been recorded. The code can easily be copied and modified as needed.

Don't Miss

  • All types
  • Articles
  • Slideshows
  • Videos
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the site, you consent to the use of cookies. For more information, please see our Cookie policy.