How to Filter Multiple Items in VBA Pivot Table (Excel)

Written by Kasper Langmann

The ability to filter multiple items in a Visual Basic for Applications (VBA) pivot table in Excel is an essential skill for anyone looking to manipulate large sets of data. This feature allows you to sift through vast amounts of information and focus on the data that is most relevant to your needs. Whether you’re a data analyst, a financial professional, or just someone who likes to stay organized, understanding how to use this feature can significantly enhance your productivity.

Understanding Pivot Tables and VBA

Pivot tables are one of the most powerful tools in Excel. They allow you to summarize, analyze, and present your data in an easy-to-understand format. With a pivot table, you can quickly see patterns and trends in your data that might be difficult to spot in raw, unprocessed data.

VBA, on the other hand, is a programming language that’s used in Excel to automate tasks. It’s a feature-rich language that can be used to create complex macros, automate data entry, and much more. When combined with pivot tables, VBA can help you to create highly customized reports that can be updated with the click of a button.

Filtering Multiple Items in a Pivot Table

Filtering multiple items in a pivot table can be done manually through the Excel interface, but when dealing with large data sets, this can be a tedious and time-consuming process. This is where VBA comes in. By using VBA, you can automate the process and filter multiple items in a pivot table with just a few lines of code.

Before we dive into the code, it’s important to understand the structure of a pivot table. A pivot table consists of four areas: the report filter area, the column label area, the row label area, and the values area. The report filter area is where you can filter your data based on one or more criteria.

Writing the VBA Code

The first step in writing the VBA code to filter multiple items in a pivot table is to declare a variable that will hold the pivot table. This is done using the Dim statement. For example, you might write:

Dim pt As PivotTable

Next, you need to set the pivot table object to the actual pivot table in your worksheet. This is done using the Set statement. For example:

Set pt = Worksheets("Sheet1").PivotTables("PivotTable1")

Once you have a reference to the pivot table, you can use the PivotFields method to access the fields in the pivot table. The PivotFields method takes one argument: the name of the field you want to access. For example, if you wanted to access a field named “Region”, you would write:

pt.PivotFields("Region")

Applying the Filter

Once you have a reference to the field you want to filter, you can use the VisibleItemsList property to set the items that should be visible in the pivot table. The VisibleItemsList property takes an array of values that should be visible. For example, if you wanted to filter the “Region” field to only show the regions “North” and “South”, you would write:

pt.PivotFields("Region").VisibleItemsList = Array("North", "South")

It’s important to note that the VisibleItemsList property will throw an error if any of the items in the array do not exist in the pivot table. To avoid this, you can use the PivotItems method to check if an item exists before trying to filter it.

Advanced Filtering Techniques

While the above method works well for simple filters, there may be times when you need to apply more complex filters. For example, you might want to filter items based on a condition, or you might want to filter items that meet multiple criteria. In these cases, you can use the AdvancedFilter method.

The AdvancedFilter method allows you to apply complex filters to your data. It takes three arguments: the type of filter, the criteria range, and the copy range. The type of filter can be either xlFilterInPlace, which applies the filter to the data in place, or xlFilterCopy, which copies the filtered data to a new location.

The criteria range is a range of cells that contains the criteria for the filter. The criteria range should include at least one row of labels and one row of criteria. The copy range is the range of cells where the filtered data should be copied. If the type of filter is xlFilterInPlace, the copy range is ignored.

Creating a Criteria Range

The first step in using the AdvancedFilter method is to create a criteria range. The criteria range is a range of cells that contains the criteria for the filter. The criteria range should include at least one row of labels and one row of criteria.

For example, if you wanted to filter a data set to only include rows where the “Region” is “North” and the “Sales” are greater than 1000, you would create a criteria range like this:

Region Sales
North  >1000

Applying the Advanced Filter

Once you have a criteria range, you can use the AdvancedFilter method to apply the filter. The AdvancedFilter method is a method of the Range object, so you need to call it on the range of cells that you want to filter.

For example, if your data is in cells A1 to B10, and your criteria range is in cells D1 to E2, you would apply the filter like this:

Range("A1:B10").AdvancedFilter _
    Action:=xlFilterInPlace, _
    CriteriaRange:=Range("D1:E2")

This will filter the range A1 to B10 in place, showing only the rows where the “Region” is “North” and the “Sales” are greater than 1000.

Conclusion

Filtering multiple items in a VBA pivot table in Excel is a powerful way to analyze and present your data. Whether you’re applying simple filters or using the AdvancedFilter method to apply complex criteria, VBA can help you to automate the process and save time. With a little practice, you’ll be able to use these techniques to create highly customized reports that meet your specific needs.