Pivot Table Issues

How to Exclude Multiple Prefixes from Excel PivotTable Filters with VBA

John WilsonJohn Wilson Nov 11, 2026 869 views

Question details

The user needs a VBA method to filter out multiple text prefixes in an Excel PivotTable, overcoming the limitation of a single filter condition.

Exclude Multiple Prefixes from an Excel PivotTable Filter with VBA
Product
Microsoft Excel
Device & OS
not provided
Scenario
Writing a VBA macro to filter PivotTable fields by excluding multiple specific string prefixes simultaneously.
Observed behavior
Excel's xlCaptionDoesNotBeginWith filter only supports a limited number of conditions directly, preventing the exclusion of multiple prefixes in one standard command.
Before you start

Ensure you have backed up your workbook and enabled the Developer tab to access the Visual Basic for Applications (VBA) editor before modifying macro code.

Solution 1Recommended

Loop Through PivotItems and Hide Matches

Iterate through all items in the PivotField and manually set their visibility to false if they match any of the excluded prefixes.

This method is highly flexible and works regardless of the number of prefixes you need to exclude. It manually checks each PivotItem's caption against an array of your chosen prefixes.

1
Open the VBA Editor

Press Alt + F11 in Excel to open the Visual Basic for Applications editor, then go to Insert > Module to create a new script.

2
Define the prefix array

Create a string array containing all the prefixes you want to exclude, for example: arrPrefixes = Array("Prefix1", "Prefix2", "Prefix3").

3
Loop through PivotItems

Write a 'For Each' loop to iterate through every PivotItem object in your target PivotField.

4
Check and hide items

Use the Left() string function to check if the item's caption starts with any prefix in your array. If a match is found, set PivotItem.Visible = False.

Loop Through PivotItems and Hide Matches
Performance Tip: For PivotTables with thousands of items, looping can be slow. Set Application.ScreenUpdating = False and PivotTable.ManualUpdate = True before running the loop to significantly speed up execution.
Free Microsoft Office alternative

Try WPS Office for Powerful Data Analysis and Macro Support

If you frequently encounter limitations in Excel or need a more cost-effective solution, WPS Office is a highly compatible, free alternative. WPS Spreadsheet supports PivotTables, advanced filtering, and VBA/macros, making your transition seamless.

  1. 1. Download and Install: Visit the official WPS website to download the free WPS Office suite and install it on your device.
  2. 2. Open Your Workbooks: Launch WPS Spreadsheet and open your existing .xlsm or .xlsx files directly without any format conversion.
  3. 3. Enable Macros: Click 'Enable Macros' when prompted, allowing you to run your existing VBA scripts and PivotTable automation seamlessly.
100% compatible with Microsoft Excel formats (.xlsx, .xlsm, .csv).Full support for PivotTables, complex formulas, and advanced data filtering.Built-in macro and VBA support for automating repetitive spreadsheet tasks.Lightweight, fast, and completely free to use with a familiar interface.
microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't xlCaptionDoesNotBeginWith accept an array of values?

The Excel VBA object model for PivotFilters is designed to accept up to two criteria using Value1 and Value2 with an xlAnd/xlOr operator. It does not support passing a dynamic array for more than two conditions in a single filter command.

Will looping through PivotItems slow down my Excel workbook?

Yes, if your PivotField contains thousands of unique items, a VBA loop setting Visible = False for each item can be noticeably slow. To improve speed, disable screen updating and set PivotTable manual calculation to true before running the loop.

Can I use Advanced Filter instead of a PivotTable filter?

Yes, you can use the Advanced Filter tool on the source data to copy only the desired records to a new location, and then build your PivotTable from that pre-filtered dataset. This completely bypasses PivotTable filtering limitations.

Does WPS Office support running my existing Excel VBA macros?

Yes, WPS Office provides excellent compatibility with Excel VBA macros. You can open your .xlsm files in WPS Spreadsheet and run your existing macros, including those manipulating PivotTables.