We Value Your Privacy

We and our partners use technology such as cookies on our site to personalise content and ads, provide social media features, and analyse our traffic. Click below to consent to the use of this technology across the web. You can change your mind and change your consent choices at anytime by returning to this site.

Update Consent
Loading ...

How to Change a Pivot Table Filter in VBA

Updated July 20, 2017

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.

Loading ...
  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. Insert a pivot table based on the data. Filter on the Color column by deselecting "Green."

  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. Click on "Insert" in the top menu bar and select the "Module" option.

  5. Type the following Visual Basic code:

  6. Public Sub FilterPivotTable()

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

  8. End With

  9. End Sub

  10. 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."

  11. Tip

    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.

Loading ...

About the Author

Linda Banks

Linda Banks began writing professionally in 2003, after spending 15 years in the business world. She has been published in "Alaska's Best Kitchens" and "The Tea Room News," specializing in business topics including planning, marketing and information technology. Banks holds a Master of Business Administration in technology management from the University of Phoenix.

Loading ...
Loading ...