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)
1 2 3 |
For Each pi In pf.PivotItems strName = pi.Value 'In my test data, all items are strings Next |
Not long at all. Under a second.
How long to work out what the current visiblestatus is of each PivotItem?
1 2 3 |
For Each pi In pf.PivotItems bStatus = pi.Visible Next |
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.
1 2 3 |
For Each pi In pf.PivotItems pi.Visible = True Next |
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?
1 2 3 |
For Each pi In pf.PivotItems pi.Visible = Not pi.Visible Next |
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.
Have at it people. Look forward to comments, feedback, suggestions, and Nobel Prize nominations.
Regards,
Jeff
Slower Approach
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 |
Sub Dictionary_Slower() FilterPivot_Dictionary_Slower End Sub Private Function FilterPivot_Dictionary_Slower(Optional rngPivotField As Range, Optional rngFilterItems As Range) As Boolean ' Copyright ©2013 Jeff Weir ' weir.jeff@gmail.com ' You are free to use this code within your own applications, add-ins, ' documents etc but you are expressly forbidden from selling or ' otherwise distributing this source code without prior consent. ' This includes both posting free demo projects made from this ' code as well as reproducing the code in text or html format. ' --------------------------------------------------------------------- ' Date Initial Details Version ' 20131113 JSW Initial Programming 007 (of course) '############# '# Remarks # '############# ' This code needs to be called by a wrapper function. ' e.g. ' Sub FilterPivot() ' FilterPivot_Dictionary_Slower ' End Sub ' If required, that wrapper function can also provide ranges ' specifying what PivotField to filter, and where the range of ' filter terms is. e.g.: ' FilterPivot_Dictionary_Slower Range("A2"), Range("C2:C20000") ' ...or ' FilterPivot_Dictionary_Slower(ActiveCell, [tblFilterItems]) Dim ptOriginal As PivotTable Dim pfOriginal As PivotField Dim pfFilterItems As PivotField Dim pi As PivotItem Dim ptFilterItems As PivotTable Dim wksTemp As Worksheet Dim wksPivot As Worksheet Dim dic As Object Dim varContinue As Variant Dim strMessage As String Dim varFormat As Variant Dim bDateFormat As Boolean Dim bDateWarning As Boolean FilterPivot_Dictionary_Slower = False 'Assume failure On Error GoTo ErrHandler Set wksPivot = ActiveSheet 'If neccessary, prompt user for the pivotfield of interest If rngPivotField Is Nothing Then On Error Resume Next Set rngPivotField = ActiveCell Set pfOriginal = rngPivotField.PivotField 'Tests if this is in fact a PivotField If Err <> 0 Then Err.Clear Set rngPivotField = Nothing Set rngPivotField = Application.InputBox( _ Title:="Where is the PivotField?", _ Prompt:="Please select a cell in the PivotField you want to filter", _ Type:=8) On Error GoTo ErrHandler If rngPivotField Is Nothing Then Err.Raise 996 End If On Error GoTo ErrHandler End If Set pfOriginal = rngPivotField.PivotField Set ptOriginal = pfOriginal.Parent 'If neccessary, prompt user for FilterItems table related to the pivotfield of interest If rngFilterItems Is Nothing Then On Error Resume Next Set rngFilterItems = Application.InputBox( _ Title:="Where are the filter items?", _ Prompt:="Please select the range where your filter terms are", _ Type:=8) On Error GoTo ErrHandler If rngFilterItems Is Nothing Then Err.Raise 996 End If Application.ScreenUpdating = False Application.Calculation = xlCalculationManual ' Excel stores dates differently between PivotItems and Variant Arrays. ' For instance: ' ? CStr(varFilterItems(i, 1)) ' 1/01/2013 ' ? pi.Value ' 1/1/2013 ' ? CStr(varFilterItems(i, 1)) = pi.Value ' False 'So we 'll turn our FilterItems into a PivotTable to ensure formats are treated the same. Set wksTemp = Sheets.Add rngFilterItems.Copy wksTemp.Range("A2") wksTemp.Range("A1").Value = "FilterItems" Set rngFilterItems = wksTemp.Range("A2").CurrentRegion On Error GoTo 0 ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ rngFilterItems).CreatePivotTable _ TableDestination:=[C1], TableName:="appFilterItems" Set ptFilterItems = wksTemp.PivotTables("appFilterItems") Set pfFilterItems = ptFilterItems.PivotFields(1) ' Add FILTERItems to a Dictionary Set dic = CreateObject("scripting.dictionary") For Each pi In pfFilterItems.PivotItems dic.Add pi.Value, 1 'The one does nothing Next ptOriginal.ManualUpdate = True 'dramatically speeds up the routine, because the pivot won't recalculate until we're done 'Check if PFOriginal is formatted as a date field. ' Basically there is a bug in Excel whereby if you try to do some things ' to a PivotItem containing a date but the PivotField number format is NOT a date format ' then you get an error. ' So we'll check the PivotField date format and see what it is ' Note that if a PivotField is based on a range that contains multiple formats ' then you get an error simply by checking what the PivotField number format is. ' So we'll instigate an On Error Resume Next to handle this On Error Resume Next varFormat = pfOriginal.NumberFormat On Error GoTo ErrHandler If IsDate(Format(1, varFormat)) Then bDateFormat = True ' Now try and add the PivotItems. ' If ther's an error, we'll know that this item is also in the FilterTerms On Error Resume Next With dic For Each pi In pfOriginal.PivotItems dic.Add pi.Value, 1 'The 1 does nothing If Err.Number <> 0 Then 'This item exists in our search term list, so we should unhide it 'Note that IF this item is a date but the PivotField format is NOT a date format, ' we can't programatically hide/show items, so we'll have to check this first If Not bDateFormat Then If Not IsNumeric(pi.Value) Then 'We need the Not IsNumeric bit above because VBA thinks that some decimals encased in strings e.g."1.1" are dates If IsDate(pi.Value) Then If Not bDateWarning Then On Error GoTo ErrHandler Err.Raise Number:=997, Description:="Can't filter dates" On Error Resume Next End If Else: If Not pi.Visible = True Then pi.Visible = True End If Else: If Not pi.Visible = True Then pi.Visible = True End If Else: If Not pi.Visible = True Then pi.Visible = True End If Err.Clear Else: If Not pi.Visible = False Then pi.Visible = False End If Next End With On Error GoTo ErrHandler FilterPivot_Dictionary_Slower = True ErrHandler: If Err.Number <> 0 Then Select Case Err.Number Case Is = 0: 'No error - do nothing Case Is = 996: 'Operation Cancelled Case Is = 997: 'Can't filter dates strMessage = "*** WARNING...I can't correctly filter dates in this Pivot ***" strMessage = strMessage & vbNewLine & vbNewLine strMessage = strMessage & "I've found at least one date in this PivotField. " strMessage = strMessage & "Unfortunately due to a bug in Excel, if you have dates " strMessage = strMessage & " in a PivotField AND that PivotField is NOT formatted " strMessage = strMessage & " with a date format, then dates " strMessage = strMessage & " can't be programatically filtered either in or out. " strMessage = strMessage & vbNewLine & vbNewLine strMessage = strMessage & " So you'll have to manually check to see whether " strMessage = strMessage & " date items appear as they should." strMessage = strMessage & vbNewLine & vbNewLine strMessage = strMessage & "Do you want me to continue anyway? " varContinue = MsgBox(Prompt:=strMessage, Buttons:=vbYesNo, Title:="Sorry, can't filter dates") If varContinue = 6 Then bDateWarning = True Resume Next Else: pfOriginal.ClearAllFilters End If Case Is = 998: 'Can't filter Datafields MsgBox "Oops, you can't filter a DataField." & vbNewLine & vbNewLine & "Please select a RowField, dicumnField, or PageField and try again.", vbCritical, "Can't filter Datafields" Case Is = 999: 'no pivotfield selected MsgBox "Oops, you haven't selected a pivotfield." & vbNewLine & vbNewLine & "Please select a RowField, dicumnField, or PageField and try again.", vbCritical, "No PivotField selected" Case Else: MsgBox "Whoops, something went wrong" End Select End If With Application If Not wksTemp Is Nothing Then .DisplayAlerts = False wksTemp.Delete .DisplayAlerts = True End If .ScreenUpdating = True .Calculation = xlCalculationAutomatic End With If Not ptOriginal Is Nothing Then ptOriginal.ManualUpdate = False End Function <h2>Faster Approach</h2> Option Explicit 'Further Development Ideas ' Date Ini Detail ' 20150605 JSW Colud potentially enhance the handling of dates in GENERAL formatted PivotFields by checking if format matches the US date layout. ' See http://www.vbforums.com/showthread.php?287517-Determining-regional-setting-in-VBA-code ' ALso, there's a potential workaround at http://dailydoseofexcel.com/archives/2013/11/09/a-date-with-pivotitems/#comment-638479 Sub FilterPivot() ' Description: Wrapper function for my FilterPivot Function ' (Required in the event that a user wants to trigger the FilterPivot ' function and specify parameters at runtime, instead of programatiacally ' calling the routine) ' Programmer: Jeff Weir ' Contact: weir.jeff@gmail.com or jeff.weir@HeavyDutyDecisions.co.nz ' Name/Version: Date: Ini: Modification: ' FilterPivot 20150305 JSW Initial programming FilterPivot_Routine End Sub Sub FilterPivot_Inverse() ' Description: Wrapper function for my FilterPivot_Inverse Function ' (Required in the event that a user wants to trigger the FilterPivot_Inverse ' function and specify parameters at runtime, instead of programatiacally ' calling the routine) ' Programmer: Jeff Weir ' Contact: weir.jeff@gmail.com or jeff.weir@HeavyDutyDecisions.co.nz ' Name/Version: Date: Ini: Modification: ' FilterPivot_Inverse 20150305 JSW Initial programming FilterPivot_Routine bInverse:=True End Sub Function FilterPivot_AddRightClick() ' Description: Adds "Filter Pivotfield" shortcuts to PivotTable right-click menu ' Programmer: Jeff Weir ' Contact: weir.jeff@gmail.com or jeff.weir@HeavyDutyDecisions.co.nz ' Name/Version: Date: Ini: Modification: ' AddShortcuts 20150305 JSW Initial programming Dim cbr As CommandBar Dim sMessage As String FilterPivot_RemoveRightClick Set cbr = Application.CommandBars("PivotTable Context Menu") With cbr.Controls.Add(Type:=msoControlButton, Temporary:=True) .Caption = "Filter Pivotfield" .Tag = "FilterPivotField" .OnAction = "FilterPivot" .Style = msoButtonIconAndCaption .Picture = Application.CommandBars.GetImageMso("FilterAdvancedByForm", 16, 16) End With With cbr.Controls.Add(Type:=msoControlButton, Temporary:=True) .Caption = "Inversely Filter Pivotfield" .Tag = "FilterPivotField" .OnAction = "FilterPivot_Inverse" .Style = msoButtonIconAndCaption .Picture = Application.CommandBars.GetImageMso("FilterAdvancedMenu", 16, 16) End With End Function Function FilterPivot_RemoveRightClick() ' Description: Removes "Filter Pivotfield" shortcuts from PivotTable right-click menu ' Programmer: Jeff Weir ' Contact: weir.jeff@gmail.com or jeff.weir@HeavyDutyDecisions.co.nz ' Name/Version: Date: Ini: Modification: ' AddShortcuts 20150305 JSW Initial programming Dim cbr As CommandBar Dim sMessage As String Dim ctrl As CommandBarControl Set cbr = Application.CommandBars("PivotTable Context Menu") For Each ctrl In cbr.Controls If ctrl.Tag = "FilterPivotField" Then ctrl.Delete Next End Function Private Function FilterPivot_Slicers(ptTemp As PivotTable, pfTemp As PivotField, ptOriginal As PivotTable) Dim sc As SlicerCache ' This sub-function contains code that requires Slicers ' It's in this stand-alone function, becaues it only gets called if user has Excel 2010 or later ' If it was incorporated DIRECTY within the FilterPivot function, that function wouldn't compile on pre 2010 machines Set sc = ActiveWorkbook.SlicerCaches.Add(ptTemp, pfTemp) sc.PivotTables.AddPivotTable ptOriginal 'Great, our original pivot now just has one item visible in the field of interest 'So we can delete the slicer connection sc.Delete End Function Private Function FilterPivot_Routine(Optional rngPivotField As Range, Optional rngFilterItems As Range, Optional bInverse As Boolean = False) As Boolean ' Copyright ©2013 Jeff Weir ' weir.jeff@gmail.com ' You are free to use this code within your own applications, add-ins, ' documents etc but you are expressly forbidden from selling or ' otherwise distributing this source code without prior consent. ' This includes both posting free demo projects made from this ' code as well as reproducing the code in text or html format. ' --------------------------------------------------------------------- ' Date Initial Version Details ' 20131113 JSW 007 Initial Programming ' 20131203 JSW 008 Added Inverse Option ' 20140503 JSW 009 Changed so works in pre Excel 2010 ' 20140503 JSW 010 Changed so pi.format check not performed in Excel 2013 + ' 20150223 JSW 011 Added check to ensure pfOriginal has .EnableMultiplePageItems set to TRUE if it is a pagefield. ' 20150429 JSW 012 Turned off events, cleared pfOriginal filter. ' 20150605 JSW 013 Fixed “Save source data with file” reset as per http://dailydoseofexcel.com/archives/2013/11/14/filtering-pivots-based-on-external-ranges/#comment-787682 '############# '# Remarks # '############# ' This code needs to be called by a wrapper function. ' e.g. ' Sub FilterPivot() ' FilterPivot_Routine ' End Sub ' Sub FilterPivot_Inverse() ' FilterPivot_Routine bInverse:=True ' End Sub ' If required, that wrapper function can also provide ranges ' specifying what PivotField to filter, and where the range of ' filter terms is. e.g.: ' FilterPivot_Routine Range("A2"), Range("C2:C20000") ' ...or ' FilterPivot_Routine ActiveCell, [tblFilterItems] Dim ptOriginal As PivotTable Dim ptTemp As PivotTable Dim pfOriginal As PivotField Dim pfTemp As PivotField Dim pfFilterItems As PivotField Dim lngFilterItems As Long Dim pi As PivotItem Dim ptFilterItems As PivotTable Dim wksTemp As Worksheet Dim wksPivot As Worksheet Dim dic As Object Dim varContinue As Variant Dim strMessage As String Dim varFormat As Variant Dim bDateFormat As Boolean Dim bDateWarning As Boolean Dim bFirstItemVisible As Boolean Dim varFirstItemVisible As Variant Dim bEnableEvents As Boolean Dim bScreenUpdating As Boolean Dim lngCalculation As Long Dim bSaveDataState As Boolean FilterPivot_Routine = False 'Assume failure On Error GoTo errhandler Set wksPivot = ActiveSheet 'If neccessary, prompt user for the pivotfield of interest If rngPivotField Is Nothing Then On Error Resume Next Set rngPivotField = ActiveCell Set pfOriginal = rngPivotField.PivotField 'Tests if this is in fact a PivotField If Err <> 0 Then Err.Clear Set rngPivotField = Nothing Set rngPivotField = Application.InputBox( _ Title:="Where is the PivotField?", _ Prompt:="Please select a cell in the PivotField you want to filter", _ Type:=8) On Error GoTo errhandler If rngPivotField Is Nothing Then Err.Raise 996 End If On Error GoTo errhandler End If Set pfOriginal = rngPivotField.PivotField Set ptOriginal = pfOriginal.Parent 'Capture the SaveData state of the original pivot table bSaveDataState = ptOriginal.SaveData With pfOriginal If .Orientation = xlPageField Then If .EnableMultiplePageItems = False Then .EnableMultiplePageItems = True .ClearAllFilters End If End If End With 'If neccessary, prompt user for FilterItems table related to the pivotfield of interest If rngFilterItems Is Nothing Then On Error Resume Next Set rngFilterItems = Application.InputBox( _ Title:="Where are the filter items?", _ Prompt:="Please select the range where your filter terms are.", _ Type:=8) On Error GoTo errhandler If rngFilterItems Is Nothing Then Err.Raise 996 End If With Application bScreenUpdating = .ScreenUpdating bEnableEvents = .EnableEvents lngCalculation = .Calculation .ScreenUpdating = False .EnableEvents = False .Calculation = xlCalculationManual End With ' Excel stores dates differently between PivotItems and Variant Arrays. ' For instance: ' ? CStr(varFilterItems(i, 1)) ' 1/01/2013 ' ? pi.Value ' 1/1/2013 ' ? CStr(varFilterItems(i, 1)) = pi.Value ' False 'So we 'll turn our FilterItems into a PivotTable to ensure formats are treated the same. Set wksTemp = Sheets.Add rngFilterItems.Copy wksTemp.Range("A2") wksTemp.Range("A1").Value = "FilterItems" Set rngFilterItems = wksTemp.Range("A2").CurrentRegion On Error GoTo errhandler ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ rngFilterItems).CreatePivotTable _ TableDestination:=[C1], TableName:="appFilterItems" Set ptFilterItems = wksTemp.PivotTables("appFilterItems") Set pfFilterItems = ptFilterItems.PivotFields(1) ' Add FILTERItems to a Dictionary Set dic = CreateObject("scripting.dictionary") For Each pi In pfFilterItems.PivotItems dic.Add pi.Value, 1 'The one does nothing Next ptOriginal.ManualUpdate = True 'dramatically speeds up the routine, because the pivot won't recalculate until we're done 'Check if we're dealing with Excel 2010 or earlier and if PFOriginal is formatted as a date field. ' Basically there is a bug in Excel 2010 or earlier whereby if you try to do some things ' to a PivotItem containing a date but the PivotField number format is NOT a date format ' then you get an error. ' So we'll check the PivotField date format and see what it is ' Note that if a PivotField is based on a range that contains multiple formats ' then you get an error simply by checking what the PivotField number format is. ' So we'll instigate an On Error Resume Next to handle this If Application.Version < 15 Then On Error Resume Next varFormat = pfOriginal.NumberFormat On Error GoTo errhandler If IsDate(Format(1, varFormat)) Then bDateFormat = True End If 'Work out maximum amount of filteritems under bInverse scenario If bInverse Then lngFilterItems = pfOriginal.PivotItems.Count - rngFilterItems.Count Else: lngFilterItems = rngFilterItems.Count End If If lngFilterItems / pfOriginal.PivotItems.Count < 0.5 And Application.Version >= 14 Then '==================================================================================== ' If it's likely that less than half of the source Pivot Field's ' items will be visible when we're done, then it will be quickest to hide all but one ' item and then unhide the PivotItems that match the filter terms ' Iterating through a large pivot setting all but one item to hidden is slow. ' And there's no way to directly do this except in Page Fields, and ' that method doesn't let you select multiple items anyway. ' Plus, as soon as you drag a page field with just one item showing to ' a row field, Excel clears the filter, so that all items are visible again. ' So we'll use a trick: ' * make the pf of interest in ptTemp a page field ' * turn off multiple items and select just one PivotItem ' * connect it to the original pivot with a slicer ' This will very quickly sync up the field on the original pivot so that only one field is showing. ' NOTE: WE CAN ONLY DO THIS IF USING EXCEL 2010 OR LATER using the below approach. ' If earlier version, we'll use the approach outlined in the ELSE part of this IF block. ' ALSO NOTE: If a PivotField has a non-Date format, but contains dates, then ' we can't programatically hide/show items. So we need to check for this. '==================================================================================== 'Identify a suitable item with which to filter the original PivotTable with ' As per note above, ' * If the PivotField format is NOT a date format, ' then we need to make sure that this first item is NOT a date. ' ...because otherwise we can't address it by VBA ' * If the PivotFied format IS a date format, then just use the first item. ' * We'll write that item to a range, then to a variant, so that Excel applies the ' same format to it as it does to items in our Filter list If Not bDateFormat Then For Each pi In pfOriginal.PivotItems If IsDate(pi.Value) Then If IsNumeric(pi.Value) Then 'We need the IsNumeric bit above because 'VBA thinks that some decimals encased in strings e.g. "1.1" are dates 'So we need to check whether this is a decimal and NOT a date varFirstItemVisible = pi.Value Exit For Else: If Not bDateWarning Then Err.Raise Number:=997, Description:="Can't filter dates" End If End If Else: varFirstItemVisible = pi.Value Exit For End If Next Else: varFirstItemVisible = pfOriginal.PivotItems(1).Value End If Set ptTemp = ptOriginal.PivotCache.CreatePivotTable(TableDestination:=wksTemp.Range("F1")) Set pfTemp = ptTemp.PivotFields(pfOriginal.SourceName) 'Set the SaveData state the same as the original pivot table in case it is set to False 'When creating a new pivot table, by default it is set to True, which will carry over to the original pivot table ptTemp.SaveData = bSaveDataState With pfTemp .Orientation = xlPageField .ClearAllFilters .EnableMultiplePageItems = False .CurrentPage = pfTemp.PivotItems(varFirstItemVisible).Value End With Call FilterPivot_Slicers(ptTemp, pfTemp, ptOriginal) ' Check if FirstItemVisible should be visible or hidden when we are done If dic.Exists(varFirstItemVisible) Then bFirstItemVisible = True ' Now try and add the PivotItems. ' If there's an error, we'll know that this item is also in the FilterTerms On Error Resume Next With dic 'The Not bInverse bit in the code blocks below effectively 'flip' the test "If Err.Number <> 0" to "If Err.Number = 0" 'in the case that bInverse argument is TRUE (meaning we want the Pivot to be filtered on things ' NOT in the list of search terms) If Application.Version >= 15 Then For Each pi In pfOriginal.PivotItems dic.Add pi.Value, 1 'The 1 does nothing If Err.Number <> 0 = Not bInverse Then pi.Visible = True End If Err.Clear Next Else: 'There's a bug in previous versions where you can't use .Visible for dates 'if the PivotField is set to General format. For Each pi In pfOriginal.PivotItems dic.Add pi.Value, 1 'The 1 does nothing If Err.Number <> 0 = Not bInverse Then ' This item exists in our search term list, so we should unhide it ' Note that due to a bug in Excel 2010, if this item is a date ' but the PivotField format is NOT a date format, we can't ' programatically hide/show items, so we'll have to check this first If Not bDateFormat Then If Not IsNumeric(pi.Value) Then 'We need the Not IsNumeric bit above because VBA thinks that ' some decimals encased in strings e.g."1.1" are dates If IsDate(pi.Value) Then If Not bDateWarning Then On Error GoTo errhandler Err.Raise Number:=997, Description:="Can't filter dates" On Error Resume Next End If Else: pi.Visible = True End If Else: pi.Visible = True End If Else: pi.Visible = True End If End If Err.Clear Next End If 'If Application.Version >= 15 Then End With If Not bFirstItemVisible = Not bInverse Then pfOriginal.PivotItems(varFirstItemVisible).Visible = False If Err.Number <> 0 Then MsgBox "None of the filter items were found in the Pivot" pfOriginal.ClearAllFilters Err.Clear End If End If Else: ' If it's likely that MORE than half of the source Pivot Field's items will be visible ' when we're done, then it's quickest to unhide all PivotItems and then hide the ' PivotItems that DON'T match the filter terms pfOriginal.ClearAllFilters ' Now try and add the PivotItems. ' If there's an error, we'll know that this item is in the FilterItems ' Otherwise we'll hide it On Error Resume Next With dic If Application.Version >= 15 Then 'Excel 2010 and earier have some 'issues' with dates For Each pi In pfOriginal.PivotItems dic.Add pi.Value, 1 'The 1 does nothing If Err.Number = 0 = Not bInverse Then pi.Visible = False Err.Clear Next Else: For Each pi In pfOriginal.PivotItems dic.Add pi.Value, 1 'The 1 does nothing If Err.Number = 0 = Not bInverse Then 'The Not bInverse bit effectively 'flips' the test "If Err.Number = 0" to "If Err.Number <> 0" 'in the case that bInverse argument is TRUE (meaning we want the Pivot to be filtered on things ' NOT in the list of search terms) 'This PivotItem NOT in FilterItems list. So hide it 'Note that IF this item is a date but the PivotField format is NOT a date format, ' then we can't programatically hide/show items, so we'll have to check this first If Not bDateFormat Then If Not IsNumeric(pi.Value) Then 'We need the Not IsNumeric bit above because VBA thinks that some decimals encased in strings e.g."1.1" are dates If IsDate(pi.Value) Then If Not bDateWarning Then On Error GoTo errhandler Err.Raise Number:=997, Description:="Can't filter dates" On Error Resume Next End If Else: pi.Visible = False 'This item does not exist in the FilterItems. So hide it End If Else: pi.Visible = False 'This item does not exist in the FilterItems. So hide it End If Else: pi.Visible = False End If End If Err.Clear Next End If 'If Application.Version >= 15 Then End With End If 'If lngFilterItems / pfOriginal.PivotItems.Count < 0.5 And Application.Version >= 14 Then On Error GoTo errhandler FilterPivot_Routine = True errhandler: If Err.Number <> 0 Then Select Case Err.Number Case Is = 0: 'No error - do nothing Case Is = 996: 'Operation Cancelled Case Is = 997: 'Can't filter dates strMessage = "*** WARNING...I can't correctly filter dates in this Pivot ***" strMessage = strMessage & vbNewLine & vbNewLine strMessage = strMessage & "I've found at least one date in this PivotField. " strMessage = strMessage & "Unfortunately due to a bug in Excel, if you have dates " strMessage = strMessage & " in a PivotField AND that PivotField is NOT formatted " strMessage = strMessage & " with a date format, then dates " strMessage = strMessage & " can't be programatically filtered either in or out. " strMessage = strMessage & vbNewLine & vbNewLine strMessage = strMessage & " So you'll have to manually check to see whether " strMessage = strMessage & " date items appear as they should." strMessage = strMessage & vbNewLine & vbNewLine strMessage = strMessage & "Do you want me to continue anyway? " varContinue = MsgBox(Prompt:=strMessage, Buttons:=vbYesNo, Title:="Sorry, can't filter dates") If varContinue = 6 Then bDateWarning = True Resume Next Else: pfOriginal.ClearAllFilters End If Case Is = 998: 'Can't filter Datafields MsgBox "Oops, you can't filter a DataField." & vbNewLine & vbNewLine & "Please select a RowField, ColumnField, or PageField and try again.", vbCritical, "Can't filter Datafields" Case Is = 999: 'no pivotfield selected MsgBox "Oops, you haven't selected a pivotfield." & vbNewLine & vbNewLine & "Please select a RowField, ColumnField, or PageField and try again.", vbCritical, "No PivotField selected" Case Else: MsgBox "Whoops, something went wrong" End Select End If With Application If Not wksTemp Is Nothing Then .DisplayAlerts = False wksTemp.Delete .DisplayAlerts = True End If .ScreenUpdating = bScreenUpdating .EnableEvents = bEnableEvents .Calculation = lngCalculation End With If Not ptOriginal Is Nothing Then ptOriginal.ManualUpdate = False End Function |
I wrote about end user defined named sets on pivots which is kind of similar. Check it out if you’d like http://blog.stevienova.com/2009/07/12/excel-2007-olap-cubes-customizable-user-defined-named-sets-in-excel-2007-using-vba/
Or, you use the technique shown in this post.
http://www.powerpivotpro.com/2011/04/calculating-a-sum-based-on-a-list-criteria/
Along the same lines as David, I think it’s much easier to just create a helper column in your source data that looks to the table, and then filter the pivot on that helper column.
Just so happens I’ve got a post for that: http://yoursumbuddy.com/filter-pivot-tables-using-source-data-helper-columns/ :)
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.
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.
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.
Yes olap
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?
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.
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!
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.
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.
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
Thanks Frank. I’m on holiday at present, but I’ll take your revision for a spin when I get back.
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.
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.
Nobel Prize D.A.? Really?
Try PivotTable.ManualUpdate = True
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:
If you can write faster code to do this, I’d love to see it.
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 ?
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.
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
snb: Yes, your code is much shorter than mine. But my code is not longer just for the sake of it.
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.
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
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?
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
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.
@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.
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 :-)
Great catch, Rudi. Will amend post accordingly. Keep ’em coming!
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 ;-)
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.
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
Justi: It works just fine for me. Are you still having difficulties? If so, flick me a line at weir.jeff@gmail.com and I’ll try to work out what the issue is.
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
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.
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
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.
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!
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?
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?
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.
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!
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.
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.
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…
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!”
http://dailydoseofexcel.com/archives/2015/11/17/filtering-pivottables-with-vba-deselect-slicers-first/
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!
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.
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?
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
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!