Return to VBA Code Examples

VBA – Pivot Table Filter

This tutorial will demonstrate how to use the Pivot Table Filter in VBA.

Pivot tables are an exceptionally powerful data tool of Excel. Pivot tables enable us to analyze and interpret large amounts of data by grouping and summarizing fields and rows. We can apply filters to our pivot tables to enable us to quickly see the data that is relevant to us.

Firstly, we need to create a Pivot table for our data.  (Click Here for our VBA Pivot Table Guide)

VBA Filter Pivot

Creating a Filter Based on a Cell Value

In this example we will filter a Pivot Table based on a cell value.

In an empty cell to the right of the Pivot table, create a cell to hold the filter, and then type the data into the cell that you wish to filter the Pivot table on.

VBA Filter Page Filter

 

Create the following VBA Macro:

Run the macro to apply the filter.

VBA Filter Page Filtered

To clear the filter, create the following macro:

The filter will then be removed.

We can then amend the filter criteria to filter on a row in the Pivot table rather than the Current Page.

VBA Filter CellValue

 

Typing the following macro will then enable us to filter on the Row (note that the Pivot Field to filter on is now the Oper rather than the Supplier).

Run the macro to apply the filter.

VBA Filter RowFilter

Using Multiple Criteria in a Pivot Filter

We can add to the Row value filter above by adding additional criteria.

VBA Filter MultipleRow

However, as the standard filter hides the rows that are not required, we need to loop through the criteria and show the ones that are requited, while hiding the ones that are not required. This is done by creating an Array variable and using a couple of Loops in the code.

Creating a Filter Based on a Variable

We can use the same concepts to create filters based on variables in our code rather than the value in a  cell. This time, the filter variable (strFilter) is populated in the code itself (eg: Hard-coded into the macro).

 

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users! vba save as


Learn More!