How to Exclude Multiple Prefixes from Excel PivotTable Filters with VBA
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.

- 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.
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.
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.
Press Alt + F11 in Excel to open the Visual Basic for Applications editor, then go to Insert > Module to create a new script.
Create a string array containing all the prefixes you want to exclude, for example: arrPrefixes = Array("Prefix1", "Prefix2", "Prefix3").
Write a 'For Each' loop to iterate through every PivotItem object in your target PivotField.
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.

Use a Helper Column in the Source Data
Add a new column to your source data that flags rows containing the excluded prefixes, then filter the PivotTable using this new column.
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. Download and Install: Visit the official WPS website to download the free WPS Office suite and install it on your device.
- 2. Open Your Workbooks: Launch WPS Spreadsheet and open your existing .xlsm or .xlsx files directly without any format conversion.
- 3. Enable Macros: Click 'Enable Macros' when prompted, allowing you to run your existing VBA scripts and PivotTable automation seamlessly.

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.




