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.

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()

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

End With

End Sub

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

Most recent