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.
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.
- An Excel pivot table is a powerful solutions tool.
- For Columns B and C, type in random letters and numbers to create the data table.
Insert a pivot table based on the data. Filter on the Color column by deselecting "Green."
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."
Click on "Insert" in the top menu bar and select the "Module" option.
Type the following Visual Basic code:
Public Sub FilterPivotTable()
- Insert a pivot table based on the data.
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."
- 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.
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.