Filtering Pivots based on external ranges.

Howdy, folks. Jeff here again. We might as well rename this blog Daily Dose of Pivot Tables, because here’s yet another treatment of this well-worn subject this week.

Let’s say you’ve got a PivotField with 20,000 items in it. What’s the quickest way to filter that PivotTable based on an external list that contains either 100, 10000, or 19900 of those items?
The usual approach to a task like this is to just iterate through each pivotitem in the PivotItems collection, and check if that PivotItem is in the list of search terms. If it is, you make the PivotItem visible, otherwise you hide it.

But this requires us to:

  • Read the PivotItem name
  • Read in the Search Terms
  • Check if each PivotItem is in the list of search terms. If it is, you make the PivotItem visible, otherwise you hide it.

How long does each part of this take? Where are the bottlenecks? Let’s find out.

First, how long does it take just to iterate through all 20,000 items and get the value of each PivotItem? (Note that I’ve set pt.ManualUpdate = True before running all the following snippets, so that the PivotTable doesn’t try to update after each and every change)

Not long at all. Under a second.

How long to work out what the current visiblestatus is of each PivotItem?

One minute and twenty seconds? Really? Not exactly lightning fast, is it?

Okay, how long does it take to set the .Visible property of each item, without checking what it currently is? Let’s set .visible = true for each and every PivotItem (even though they are already visible) just to find out what the worst case scenario is.

Two minutes, 43 seconds. So it takes longer to set the .visible status than to read it. Handy to know.

Okay, how long will it take to first check the .Visible property of each item, and then change it?

Four minutes, 26 seconds. Not surprising I guess, because it’s got to first find the current state of each item – which we already established above takes around one minute and twenty seconds – and then we need to change the state – which we already established above takes around 2 minutes 43 seconds. And those two times add up to 4 minutes.

So that’s how long it would take in principle to filter the PivotTable based on the initial approach I suggested above, excluding the time taken to actually check for duplicates between the PivotItems and the search terms.

Ahhh… I hear you say (I have good ears). What if we first check whether the .visible status of a PivotItem is already set how we want it. That way, we can save some time by only changing it if it actually needs to be changed. Good point, and nice to see you’re alert.

So here’s our efficient tweak of the ‘traditional’ method:

  • Add all Search terms to a dictionary (or collection, if you prefer)
  • Try to add each PivotItem to that same dictionary.
  • If that last step caused an error, we’ll know that this PivotItem is in our list of search terms. In this case, we can check what the current visible status is of the PivotItem. If it’s NOT visible, we’ll make it visible. If it IS visible, we do nothing
  • IF this didn’t cause an error, we’ll know that this PivotItem IS NOT in our list of filter terms. In this case, we again check what the current visible status is of the PivotItem. If it’s visible, we’ll hide it. If it’s already hidden, we do nothing

So this approach is quite efficient in that it only changes the .visible status of the PivotItem if it has to. Which is good, because this is the bottleneck. And the general approach of using a Dictionary (or collection) is very efficient, compared to other ways I’ve seen on line that use say applicaiton.match to check the PivotItem against a variant array or (far worse) against the original FilterTerms range in the worksheet.

On a pivot of 20,000 items that currently has all items visible, here’s how this ‘tweaked traditional’ method performed:

  • It took 4:21 to filter on a list of 100 terms. When I ran it again without clearing the filter, it only took 1:32. That faster time is because it didn’t have to change the .visible status of any items at all, because they were already in the ‘correct’ state after the last run. But it still had to check them all
  • It took 3:03 to filter on a list of 10,000 terms. The shorter time compared to the first test case is because it only had to set the .visible status of half the pivot items. It took 1:35 when I ran it again without clearing the filter, same as before. That’s what I would expect.
  • It took 1:35 again to filter on a list of 19900 items – the same as the 2nd pass in the other cases, which again is what I would expect given it only had to hide a few items. And of course it took about the same time again when I ran it again without clearing the filter, same as before.

(Note that my ‘tweaked traditional’ routine has some extra checks to handle errors caused by dates in PivotFields not formatted as Date Fields, something I discussed here. And it also has to do some extra checking for PivotItems such as “1.1”, because VBA’s IsDate function interprets such a string as a date. But the extra processing time of these extra loops is pretty inconsequential compared to checking and changing the .Visible status.)

Can we do better than that?

Of course we can. What if we work out how many PivotItems in the PivotField, and how many search terms in the Search list, and either make all PivotItems visible or all PivotItems (bar one) hidden before we start, so that we minimise the amount of PivotItems we have to change the .Visible status of?

  • If there’s just 100 items in our Search Terms list, hide all but one PivotItem, then unhide just the 100 matching items
  • If there’s 19900 items in our Search Terms list, make all PivotItems visible, then hide the 100 PivotItems that are not in the Search Terms list
  • Because we know in advance whether all PivotItems are visible or hidden, we don’t have to check their .visible status at all.

Genius in theory, I know. And it’s certainly trivial to clear a PivotFilter so that all items are visible in that 2nd case. But that 1st case is tricky: how do you hide all but one PivotItem via VBA without iterating through and having to do all that incredibly slow .visible = false stuff? You can do it manually very easily of course. But via VBA? You can’t do it directly except if you make the field a Page Field and set .EnableMultiplePageItems to False. And then as soon as you change it to True again, VBA helpfully clears the filter so that all items are visible again. And so you’re back to square 1.

Enter the slicer

It turn out that you can very quickly hide all but one PivotItem programatically if you make a temp copy of the Pivot, make the field of interest in the temp into a Page field with .EnableMultiplePageItems set to False, and then hook it up via a slicer to your original Pivot. This forces the original PivotField to have the same filter setting – just one item visible. But it doesn’t make that original Pivot have the same layout. So the original pivot can still be say a Row field where you can then merrily make additional items visible.

How fast is this approach? Very. Again, using a test pivot with 20,000 items in it:

  • Filter on 100 search terms: 0:05 (compared with 4:21 in the approach above)
  • Filter on 10,000 search terms: 1:26 (compared with 3:03 in the approach above)
  • Filter on 19,900 search terms: 0:03 (compared with 1:35 in the approach above)

Now that is some improvement.

Here’s the two routines below for your viewing pleasure. I turned both routines into functions, which you call by a wrapper. This lets you pre-specify what PivotField you want to filter and where your search terms are. Otherwise you’ll be prompted to select them via some input boxes.

Also check out the attached workbook that has the code inside, and that lets you generate random alphanumeric PivotFields and Search Terms in a jiffy (something I’ll cover in a future post). Just click the Create Sample Data button after changing the input parameters, and then click on the command button of choice. When you run the code from the command buttons, the times of each pass will be recorded in the workbook too, so you can compare different settings.

Filter PivotTable 20131114

Have at it people. Look forward to comments, feedback, suggestions, and Nobel Prize nominations.

Regards,
Jeff

Slower Approach

63 thoughts on “Filtering Pivots based on external ranges.

  1. Hi Steve. So I take it your Pivot must be based on an OLAP data source for this to work? I get a run-time error 1004 (Application-defined or object-defined error) when I try and run your example in your sample file in Excel 2010.

  2. Hi David. Yep, great approach if you have PowerPivot installed. Where I work, we’re not allowed to install it even though we have the requisite license, I’m afraid. And of course the more people that adopt 2013, the less that will have PowerPivot under the current licensing agreement.

  3. Hi Doug. Forgot to mention that approach, which is pretty bulletproof, assuming the pivot’s source data is in the same workbook. If the data is not in the same workbook, I guess you would have to extract the data using the ol’ doubleclick on the Grand Total method, then re-point the Pivot at the extracted data, then set up the file as you’ve done.

    Thanks for reminding me. I see I left an earlier version of my code in your comments way back then. The beauty about the above code approach is that it is lightning fast to execute – no setup time at all. So it sits in my Personal Macro Workbook just itching for a Pivot to cross it’s path. And in most cases I imagine that users will be filtering on a search list of just a few hundred items at most, which takes just a few seconds. And because I turned it into a function, I can use it as part of larger routines pretty effortlessly.

  4. Hi Jeff,

    Looks very interesting. Your line:

    ptOriginal.ManualUpdate = True ‘dramatically speeds up the routine, because the pivot won’t recalculate until we’re done

    sent me off on a tangent. I hope you don’t mind me asking this question here as slicers aren’t available in Excel 2003.

    I have many Excel 2003 VBA processes that would benefit from this – if it worked. The official documentation for 2003 says the property is read/write but I can’t seem to change it from False at all. I’ve been googling all afternoon to see if anyone has a definitive answer but to no avail. I suppose if it doesn’t work for 2003 at least it wouldn’t do any harm and would yield benefits for later versions. Do you have any experience of this or do you know of any alternative approaches to .ManualUpdate that would work for people with Excel 2003?

  5. I’m pretty sure that the docementation is wrong – that line should work just fine in 2003.

    I have a version of this code that does pretty much the same thing without slicers. There’s a few issues with it, but I might be able to sort them. If I do, I’ll post it here.

  6. Jeff,

    Just to be clear, the 2003 documentation says .ManualUpdate is read/write. So the documentation says that ptOriginal.ManualUpdate should work just fine in 2003. If the documentation is wrong the line will not work in 2003. That seems to be what I’m experiencing – .ManualUpdate seems to be read-only and permanently set to False.

    Great work Jeff. I really appreciate the time you and others put into posts such as this one. So much to learn!

  7. Thanks, Ian. You’re right there’s a lot to learn. Which is why I tinker away at projects like this one. This code I’ve been working on in some shape or form for several years, and it’s only now that I’m starting to feel that I’m fluent in VBA. Mostly I learn by constant testing, tinkering, and then recoding. And occasionally I go back through the archives of resources like this blog to have a fresh look at stuff that was over my head last time I checked.

  8. Jeff,

    unfortunately in Excel 2013 – whenever “Case Is < 0.5" – the fast version yields error 1004 on line "sc.PivotTables.AddPivotTable ptOriginal". PivotField is Region or Customer.

  9. Jeff,

    without really knowing what I’m doing, I have added a line to your code. Now, it works just fine. A real masterpiece!

    Set sc = ActiveWorkbook.SlicerCaches.Add(ptTemp, pfTemp)
    ADDED: sc.PivotTables.RemovePivotTable ptTemp
    sc.PivotTables.AddPivotTable ptOriginal

    btw, I would also change
    Select Case rngFilterItems.Count / pfOriginal.PivotItems.Count
    to
    Select Case (rngFilterItems.Count – 1) / pfOriginal.PivotItems.Count
    as CurrentRegion includes the heading

  10. Hi Frank. I don’t get the same error as you. Can you email me a sample dataset where you get this issue to weir.jeff@gmail.com so I can take a closer look?

    That said, testing did find another issue whereby if there is already a slicer in the workbook for the field of interest, that slicer ends up being disconnected. So I’ll make a suitable revision and post back here.

  11. With regards to your Select Case (rngFilterItems.Count – 1) / pfOriginal.PivotItems.Count tweak, this isn’t really neccessary… I’m just after the rough ‘breakeven’ point where it will likely be faster to do something one way vs the other, and that rough breakeven point will be roughly around the point where we are trying to filter more than or less than half. But the exact breakeven point will depend on what kind of data is in the pivot and some other factors. So being exactly precise isn’t necessary.

  12. Pingback: Filter PivotTable
  13. GMoney: I already have ptOriginal.ManualUpdate = True in both routines. In fact, right at the top of the actual writeup I mention Manual Update too:

    First, how long does it take just to iterate through all 20,000 items and get the value of each PivotItem? (Note that I’ve set pt.ManualUpdate = True before running all the following snippets, so that the PivotTable doesn’t try to update after each and every change)

    If you can write faster code to do this, I’d love to see it.

  14. If I understand you correctly:
    – you want the range in column C to be filtered by the items in column E.

    As far as I can see that can be performed by:

    Sub M_snb()
    t1 = Timer

    With Sheet1.ListObjects("tblpivotdata").Range
    .AutoFilter 1, Filter(Application.Transpose(Sheet1.Range("E11:E110")), ""), 7
    .SpecialCells(12).Copy Sheet1.Cells(10, 9)
    .AutoFilter
    End With

    MsgBox Timer - t1
    End Sub

    Or did I overlook the essence of your point ?

  15. No. I want to filter the PivotTable in Column G based on the items is Column E.

    Column C is just the source data for the pivottable in Column G.

    This whole post is about filtering PivotTables quickly.

  16. I tried to reduce your code:

    The randomized strings of 10 alphanumeric characters each:

    Sub M_snb()
    Randomize
    ‘ c00=”0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz”
    c00 = Join(Filter([transpose(if(row(48:122)96,char(row(48:122)),if(mod(row(48:122),65)>26,char(row(48:122))))))], False, False), “”)

    ReDim sp(20000, 0)
    For j = 0 To UBound(sp)
    sp(j, 0) = Join(Array(Mid(c00, Int(62 * Rnd()) + 1, 1), Mid(c00, Int(62 * Rnd()) + 1, 1), Mid(c00, Int(62 * Rnd()) + 1, 1), Mid(c00, Int(62 * Rnd()) + 1, 1), Mid(c00, Int(62 * Rnd()) + 1, 1), Mid(c00, Int(62 * Rnd()) + 1, 1), Mid(c00, Int(62 * Rnd()) + 1, 1), Mid(c00, Int(62 * Rnd()) + 1, 1), Mid(c00, Int(62 * Rnd()) + 1, 1), Mid(c00, Int(62 * Rnd()) + 1, 1)), “”)
    Next

    ListObjects(“tblPivotdata”).DataBodyRange = sp
    ListObjects(“tblFilterItems”).DataBodyRange = sp
    PivotTables(“appPivot”).RefreshTable
    End Sub

    The code to filter the Pivottable (I created the Slicer manually before):

    Sub M_snb_001()
    sn = ListObjects(“tblFilterItems”).DataBodyRange

    With ActiveSheet.PivotTables(“appPivot”)
    .ManualUpdate = True

    With ActiveWorkbook.SlicerCaches(1)
    .ShowAllItems = True
    .SlicerItems(sn(1, 1)).Selected = True

    For j = 2 To UBound(sn)
    .SlicerItems(sn(j, 1)).Selected = True
    Next
    End With

    .ManualUpdate = False
    End With
    End Sub

  17. snb: Yes, your code is much shorter than mine. But my code is not longer just for the sake of it.

    • On 1000 filter terms, my long code took 36 seconds. Your short code took 1:42.
    • On 4000 filter terms, my long code took 2:09. Your short code took 9:41.
    • Try my code on say 19900 filter terms. You’ll see that it executes just as fast as if you were filtering just 100 terms. Try the same with yours, and you’ll have to quit Execl.

    What’s more, my code is point and click. It doesn’t have object hard wired into it like yours does, meaning the user can select whichever PivotField and list of search terms they want. Any level of user can use my code without modification. And my code warns users about a potential bug regarding Dates, as outlined in the article above. Whereas yours bombs out.

    You have reduced my code to the point that it doesn’t do half of the things my code is supposed to do. It no longer robustly filters a PivotField under a range of realistic scenarios.

    I just don’t understand the point of putting up shorter code for the sake of it.

  18. Hi Jeff,

    I’ve been testing with your code on Excel 2010, and I have to say the speed is awesome (0:11 against 2:21 earlier)!

    However (you saw that coming, right? ;-), for some reason the pivot field shows the first item of the multiple items selected (and the data related to that single item) in stead of “(Multiple Items)”.
    Only when I click on the drop-down icon of that field and then just click OK (nothing else changes), the pivot field shows “(Multiple Items)” and the data reflects all visible items.

    Pivot table refreshing doesn’t help. I even cleared & rebuilt the whole pivot table in case there was a corruption of sorts, but it still didn’t help.

    Any idea how I can fix that?

    Thanks,
    Rudi

  19. Ah, forgot to mention that unless I set my original pivot field to “EnableMultiplePageItems = True” before running your macro, it will not work. Is that normal?

  20. Hi Jeff,

    Solved it; sorry to have bothered you.
    My pivot filtering uses both single choice and multiple choice filters, with the PageField option mucking things up.

    Setting ‘EnableMultiplePageItems = True’ partially solved it, but the missing ingredient was ‘ClearAllFilters’.
    My only excuse is that I must have been worn out after a whole day programming and debugging…

    In case someone else encounters the problem, here’s what I did:
    After: ‘Set ptOriginal = pfOriginal.Parent’ I added the following code (I heavily use pivot event driven code):

    Best regards,
    Rudi

  21. Hi Rudi. Glad to have sped things along for you. I’ve reworked this code significantly. I’ll email you a copy of my latest if you don’t mind, because I’d like to see if my revised code still suffers from whatever issue you were having.

  22. @All: Have amended the Faster Approach code in line with some of Rudi’s suggestions to me. (Some were already made…I just haven’t updated the code for a while.) Also added some routines that set up a right-click option in the PivotTable Context menu.

  23. Hi Jeff,

    I noticed that using this procedure, the “Save source data with file” option was reset.
    Since the data I use has to be refreshed every time I use the spreadsheet, I turned that option off. It also keeps my file size nice and small.

    Turns out that by creating a temporary pivot table, which has the “SaveData” property by default set to TRUE, that setting also carries back over to the original pivot table. All other settings like “Refresh data when opening the file” and “Number of items to retain per field” seem to be unaffected.

    Here’s my solution in case you’re interested (I included parts of the original code to show where it goes):

    1. Declare the SaveDataState variable

    2. Save the “SaveData” state of the original pivot table

    3. Set the “SaveData” state of the temporary pivot table

    That’s it… No more unauthorized changes and bloated workbooks :-)

  24. Rudi – have updated the code, and also made another slight tweak:
    I added this IF:

    …around this:

    I also added note at the top around a further development idea I had re handling an excel bug I’ve previously discussed at http://dailydoseofexcel.com/archives/2013/11/09/a-date-with-pivotitems

    If you’re at a loose end, maybe you want to take a look ;-)

  25. Jeff,

    Somehow I either never had to use dates in the pivots I used with regional Excel installations or the installations were US version based, anyway I never encountered that long running problem.

    At the moment I’m working with an Excel 2010 with regional settings for Belgium and I tried setting up the pivot table (no problem there) and running the code mentioned in the other blog posting “A date with PivotItems”:

    Trying to step through the code results in a “Run-time error ‘1004’: Application-defined or object-defined error” running

    (first time) or

    (second time) after changing the numberformat to “General”.

    Stepping through the code multiple times the error keeps flip-flopping between the first and second line after

    . I haven’t got a clue why that happens…

    As such, I could not confirm the results of the “Immediate Window” after the change to “General” and I also don’t see the name of the pivot field changing.

  26. Hello Jeff,

    I just love your code – I think it is amazing how good it works. I am using the faster approach and now I am trying to set the ranges so the user does not have to select the pivot field and the items. However, I am having issues doing this. I have tried several options, but either the Excel crashes or the error pops-up. Might you be please able to help me?

    I have tried these:
    FilterPivot_Routine ActiveSheet.Range(“C8”), ActiveSheet.Range(“J3:J4”)
    FilterPivot_Routine Range(“C8”), Range(“J3:J4”)
    FilterPivot_Routine ActiveWorkbook.Worksheets(“Account”).Range(“C8”), ActiveWorkbook.Worksheets(“Account”).Range(“J3:J4”)

    Thanks a lot

  27. Hello Jeff,

    I’m trying to use your code on a Pivot table (from an OLAP source) with 83K items in the selected filter field (I want to apply between 100 to 500 unique items to the filter), all in Excel 2010. I’m getting an Err value of 1004 at the line:

    If Not bDateFormat Then
    here–> For Each pi In pfOriginal.PivotItems
    If IsDate(pi.Value) Then

    The code at this point pauses (evaluating?) for about 10s and then errors (Err.Number is 1004) without moving to the If statement. Any thoughts on what might be causing this issue?

    Cheers
    Craig

  28. Craig: The bad news is this code doesn’t work on OLAP Pivots. The good news is that I have some code that does. The bad news is I can’t share that code, because I’m in the process of turning it into a commercial addin. The good news is that I’m turning it into a commercial addin that you can buy soon. (That’s good news for both of us). The bad news is that I can’t give you an exact time it will be available, because I’m still in the bug fixing stage (and have been for some time). The good news is that I’m sure I’ll have it out in the next month or two…sooner if you don’t mind using a beta version.

    The addin also lets you do multiple wildcard filtering of PivotTables and Tables on the fly, and also lets you invert your current filter selection. It’s a cross between the existing PivotTable filter options, a slicer, and the advanced filter. It’s pretty cool, if I do say so myself.

    I’ll flick you an email in due course with a few screenshots.

  29. Hi Jeff,
    this is great! Many thanks for the code.
    But as some of the others I will need the OLAP version, how is the progress with that and where can I buy it?
    Many thanks
    Jakub

  30. Hi Jakub. I’ve just finished a six month contract and so have finally got some spare time to put into finishing the addin. But I’m on holiday at present, plus might have a week long contract lined up already on my return. So the earliest I’m likely able to deliver a prototype is a fortnight or more. Will flick you sn email on my return.

  31. Awesome macro man, I totally don’t get why excel won’t build in “(Select All)”.Visible = False, but at least you found a pretty decent work around. I was able to use your code fairly successfully, however I have 1 question I can’t seem to solve. I would like to filter on partial values, like in your example file you have strings of 10 characters, but say I only know 5 of the 10 in string, can the macro be adapted to do that? Any help would be appreciated, and thanks again for the big boost in the first place!

  32. One other question I have, I have 2 different fields in the columns area of my pivot table, I would like the filter to check both, but so far I can only have it check one at a time, is it possible for the macro to check through both ranges?

  33. Well I basically answered the 2 questions I had, the first, by creating a macro to set up an table based on an auto filter copy of the column I have the partial matches for, then using that table which then had the full matches (for my filter items), I ran the macro above. Secondly, I didn’t need to run the two fields separately, so I just created one macro for each condition, which ended up working out perfect. But now I am left with another question as I try and improve the output. I have the original table that was used to set up the filtering of the pivot table, but say I want to go into and manually edit the filters to add or remove some. What I would like to have then is to re-extract the filter values back to update that table (or in a newly created one is fine too). Anyone have any idea on how to extract the filter selections into a table?

  34. Hi Randy. Thanks for your feedback. That is tricky. But you’re in luck: I’m working on an addin that does just that. It allows you to do wildcard filtering on PivotTables, so you can look for partial matches et cetera, and you can look for as many at a time as you like. The UI I’ve put together to do this is very cool, but it’s very tricky to accomplish behind the scenes, and as per my answer to Jakub’s question, I’m not quite finished coding it up yet.

    I’ve had to put it on hold again because I’ve signed up for a short term contract. Once that contract is done and dusted, I’ll pick it up again, and will flick you an email when I get close to having the beta available.

  35. Thanks Jeff for the reply. I am using your macro very successfully now, but have had some interesting results I am trying to work around. I have been reading up on slicers quite a bit after seeing how you used them here and really like the visual aspect of them. I have added them to my sheet. I did have to comment out the line deleting the slicer connections, as it broke the link to the slicers I have set up on the template. I have 2 slicers setup, for example, say one to filter on color (red, yellow, green) and another to filter on type (apples, peppers, bananas, potatoes). If I run the macro to filter on “red”, apples and peppers and potatoes will show (and I don’t know of any red bananas), I can extract the visible slicer items for the color (red) however, if I use the visible slicer items for the type, all of them still show up, including bananas, even though they won’t show in the table. The “apples”, “peppers” and “potatoes” will show in the slicer as dark blue, and the “bananas” will show as light blue, while in the color slicer “red” will show as dark blue, and “yellow” and “green” will be white (as they are not visible, per the macros inputs of “red”). I have found in the slicer settings the “hide items with no data” and I thought this was the trick, but while it makes the light blue items disappear from the slicer, they do not disappear from the slicer visible items list. Anyway, is not too desirable for me as it is, because after seeing the slicers, I would like to use them to add (or remove) items and revise the pivots. Any thoughts on how to get only the “dark blue” items from a the slicer data? Many thanks for all the work you have put into this already!

  36. Forgot to mention that for some reason, I have named my slicers using the Name Manager, and after running the macro, the slicer that has the column of data that had the filter items name changes back to a default name. Any idea why? I’m combing though your code looking for some reason, but can’t yet see one.

  37. Hi Randy. I think the add-in I’m working on solves your issue, but that’s assuming I’m understanding your description above. Probably best you flick me an email at weir.jeff@gmail.com with a sample file.

  38. Jeff

    Wondering if you have used the Slicer Cache property of RequireManualUpdate and if you have any sample code to understand how to assign and use this property?
    I have some code to swap various fields around on some pivot tables and have 5 slicers connected to several pivot tables and charts, but the code is very slow to swap the fields around once the slicers are connected. Without connecting them everything updates within 2 seconds, connecting them changes the run time to 20 seconds…

  39. No I haven’t used it. Will have a play. I wrote a follow-up post on poor performance when filtering pivots with slicers that Google will point you to, called “Filtering Pivots? Disconnect Slicers first!”

  40. Hi Jeff,

    I wanted to thank you again for this macro. I have used it very successfully. I do have one question that I have yet to solve. In semi-rare instance that there is no items found from the source found in the pivot data, I would prefer the pivot field select “(blank)”, rather than leave everything. I am trying to tweak you code do add that in, but keep breaking it. Any thoughts on how to add that in?

    Thanks again!

  41. Hi Randy. Good idea, but would only work if the pivot data source indeed had a (blank) field in it. Is that the case for your data? I’m unlikely to spend any time changing the above code to do this, because I have been working on a significantly revised version as part of the add-in I’m forever finishing. I put the development of that addin on hold about two months back while I worked on other projects, but it’s probably time I got back to it. Let me know if you’re interested in buying a trial version at the above email address. It’s pretty cool…it’s just not *quite* ready for public consumption yet, as I’m also tweaking it so that it handles pivots based on OLAP data sources, which is holding me up a little.

  42. I get a compile error with the the section from “If Application.Version < 15 Then" highlighted in red
    Has anyone else fathomed out if there is a problem with an easy solution?

  43. Hi Angela. I think that in some countries the version gets returned as a string. What happens if you wrap a VAL around that?
    e.g.
    If Val(Application.Version) < 15

  44. This is an old post but I’ve been looking for a way to filter pivot tables similar to this for a while. (came here through stackoverflow)

    The Slicer cache is by far the best for me. It filters a dataset with millions of lines easily in seconds.

    My circumstance might be different than in this post, but basically it is a dataset with millions of lines that I am filtering based on multiple manually entered criteria.

    I had no idea that the Slicer could be set like this so easily. Thank you!


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

Leave a Reply

Your email address will not be published.