Inversely filter a Pivot based on an external range

Howdy folks. Jeff here, with a money-saving Christmas tip. Oh, and some PivotTable code.

I recently posted a routine to filter pivots based on an external range. My code worked out whether it was fastest to either:

  1. Hide all items in the field, then unhide those Pivot Items that matched the search terms; or
  2. Unhide all items in the field, then hide those Pivot Items that don’t match the search terms.

It worked out what to hide or leave by adding the Search Terms to a Dictionary, then trying to add the Pivot Items and catching any errors. In that first case where it unhides Pivot Items that match the search terms, here’s the code that did the dictionary check on the PivotItems – after the Search Terms had already been added:

…but that seems like overkill, because the only line we want to conditionally change is that If Err.Number <> 0 Then line. The rest of the block is just fine the way it is.

So how to conditionally change just that one line? Like this:

Boy, that was simple. Adding the 2nd logical effectively flips the If Err.Number <> 0 bit to If Err.Number = 0 in the case that bInverse is TRUE.

It works a treat: I tested it on a Pivot containing the things I’m willing to buy the kids for Christmas, and an external list of things containing the presents that the kids actually want. Suffice to say I set bInverse to TRUE, and saved myself a small fortune in a few milliseconds.

And there’s your Christmas tip. Ho ho horrible, I know.

Here’s the whole amended routine:

11 thoughts on “Inversely filter a Pivot based on an external range

  1. Jeff,

    Thanks so much for making this! There’s a lot of study I want to do this one. I added this code to my Personal workbook, then I hope you don’t mind but I made some alterations: I took off the “Private” from the Right-Click subs so I could call them from other places, and commented out the MsgBox in the “FilterPivot_AddRightClick” macro because it’s going to be fired quite often. I added “FilterPivot_AddRightClick” to my Personal’s “Workbook_Open” macro, so the menu is available for any pivot workbook I open. In my Custom tab I added a link to the “FilterPivot_RemoveRightClick” in case I ever see a reason to remove that option from my menu, but right now I can’t imagine why I would.

    Thank You!

  2. I have 4 Cascading ActiveX Combo Box’s and I want to change my Pivot Table on a separate page in order to change my pivot Chart on the page where my combo boxes are. So basically i want to filter my table via selections made by each of my combo boxes. How can I adapt this?

  3. Eric: Here’s an easy non-VBA way that gets around the need for comboboxes: Use Slicers, with the ‘Hide items with no data’ option in the Slicer Settings dialog checked for each of them. When that setting is checked, only relevant items appear in all of the slicers, which is exactly what you’re trying to achieve with these combo boxes.

    Note that while it is possible to achieve what you want via VBA, because of MS’s crap object model where slicers are concerned it requires you to iterate over the Slicers.Items collection and set the .visible status for each of the items…which can be slow on large PivotTables e.g. 20k items might take you 4 minutes.

    That said, I do have a devious but horribly convoluted method that I’m working on that will let you deselect all but one SlicerItems immediately so that you can then directly unhide the ones you want. But it is pretty wild code that noone will be able to get their head around in a hurry should it error out.

    So if your PivotTable has lots of data in it, seriously consider using Slicers instead of a Combo Box for any user interaction with it.

  4. I haven’t run this in a while, but I’m now getting an error on the “FilterPivot_Slicers” function. It’s on the very first line, “Set sc=Activeworkbook.SlicerCaches.Add(ptTemp, pfTemp). Not sure what the problem is, because the error message is the “Whoops”. Help would be appreciated.

  5. I suspect the problem is that there is already a SlicerCache for that PivotCache/Pivotfield combo in the workbook. Can you tell me what happens when you mouse over ptTemp and pfTemp variables, or add a watch for them and send me a screenshot? Or even email your file to me at weir.jeff@gmail.com.

    I’m in the process of recoding this routine to make it faster still, and to also let it filter OLAP Pivots, by the way.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published. Required fields are marked *