Remove Pivot Table Filters with VBA

One of the advantages of summarizing your data in a pivot table is that you can filter the results, to focus on specific items.

There are 3 types of filters available, and you can see them in the screen shot below:

• Label Filters
• Value Filters
• Manual Filters (the check boxes)

Apply and Clear Filters

You can manually apply and clear the filters, by using the commands on the drop down list for the field heading. It’s quick and easy, and gives you control of what you’re seeing in the summary.

However, for some workbooks, you might prefer to automate the filters with a macro, and have buttons or commands to run those macros.

If you’re not familiar with pivot table programming, a good way to get started is to use the Record Macro feature, while you manually apply and clear the filters. Then, you can tweak the code, to make it flexible.

Clear Manual Filters

In this example, a couple of the Stores field check boxes were cleared, so the data for those stores is filtered out.

To get the code for clearing this filter, you can turn on the Record Macro, then click the Select All check box, so all the Stores are selected again.

Click the Stop Recording button, and then press Alt + F11, to go to the Visual Basic Editor, so see the recorded code.

The code probably looks similar to the next screen shot. Each item that had been unchecked is listed, with its Visible property changed to True.

That code would only work correctly if you always have those two items filtered each time. It won’t show any of the other items that might also be hidden.

Try the Recorder Again

Code isn’t very flexible if you have to list each item separately, and update your code every time the list of items changes.

You could use a For Each…Next loop, and check every item, and change its Visible property to True, but that would be very slow, especially in a large pivot table.

Instead, we’ll record again, and try a different way to show all the items.

• First, uncheck a couple of items, so you can record showing all the items.
• Then, turn on the Macro Recorder, and click the drop down arrow in the field heading.
• Instead of using the Select All option, click the command for “Clear Filter From…”
• Click the Stop Recording button

Check the New Code

To see the new code, press Alt + F11, to go to the Visual Basic Editor, and find the latest macro.

This time, there is just one line of code (I added a line break character, to make the screen shot narrower).

Instead of changing each item, the code simply clears out all the filters. This will affect all three types of filters – Labels, Values and Manual.

This code is much faster to run, especially for larger pivot tables.

Other Filter Commands

If you would prefer to only clear the Manual filter, you can change the code from

• ClearAllFilters
  • to
• ClearManualFilter

You can also change the code so it clears just the Value or Label Filters, by using these methods:

• ClearValueFilters
• ClearLabelFilters

Download the Sample File

To experiment with the filters and the recorded code, you can download the sample file from my Contextures website.

_____________________