Update Large Pivot Filter

[BenW71]

I have a very large pivot table including a pivot filter with many values (100+). I need to filter out to only a few of those values.

The documentation i've found and with macro record it shows this as the approach:

   ActiveSheet.PivotTables("ptFull").PivotFields("PDM").CurrentPage = "(All)"
    With ActiveSheet.PivotTables("ptFull").PivotFields("PDM")
        .PivotItems("<PERSON1>").Visible = False
        .PivotItems("<PERSON2>").Visible = False
        .PivotItems("<PERSON3>").Visible = False
        .PivotItems("<PERSON4>").Visible = False
        .PivotItems("<PERSON5>").Visible = False
        .PivotItems("<PERSON6>").Visible = False
        .PivotItems("<PERSON7>").Visible = False
        .PivotItems("<PERSON8>").Visible = False
        .PivotItems("<PERSON9>").Visible = False
        .PivotItems("<PERSON10>").Visible = False
        .PivotItems("<PERSON11>").Visible = False
        .PivotItems("<PERSON12>").Visible = False
        .PivotItems("<PERSON13>").Visible = False
        .PivotItems("<PERSON14>").Visible = False
        .PivotItems("<PERSON15>").Visible = False
        .PivotItems("<PERSON16>").Visible = False
        .PivotItems("<PERSON17>").Visible = False
        .PivotItems("<PERSON18>").Visible = False
        .PivotItems("<PERSON19>").Visible = False
        .PivotItems("<PERSON20>").Visible = False
    End With
    With ActiveSheet.PivotTables("ptFull").PivotFields("PDM")
        .PivotItems("<PERSON21>").Visible = False
[B]'AND ON AND ON AND ON for everyone NOT wanted[/B]
    End With
 ActiveSheet.PivotTables("ptFull").PivotFields("PDM").EnableMultiplePageItems =True

This will work, and to do it programatically i can loop through pivotitems instead of hard coding it... But it is very inefficient even if i turn off application redraw and application calculation.

Is there any better way to set the pivot filter values instead of a loop like this?

Thanks,
- Ben

 

[SanjayGMusafir]

I had kind of similar challenge and got solution on this forum itself. Probably it works for you too.

Check this and revert -

VBA to Filter Column Labels using Filter List

Hi Everyone I need to filter my pivot table column label for Column "D1". I need this because everytime there is new item in D1, pivot table automatically clears all filters and starts showing all items. Just to keep a record that which items I need to be seen, I created a list "PFilters" with...

www.mrexcel.com

 

[BenW71]

Thanks, but it looks like that's still doing them one at a time in a loop and is what is slow (I turn off screen updating and application refresh before the loop). I already have code to loop through the array and filter based on the list.

i was hoping there was a more efficient execution other than setting 100+ pivot items to false and leaving the 5-10 as visible.

Thanks!

 

[SanjayGMusafir]

Thanks, but it looks like that's still doing them one at a time in a loop and is what is slow (I turn off screen updating and application refresh before the loop). I already have code to loop through the array and filter based on the list.

i was hoping there was a more efficient execution other than setting 100+ pivot items to false and leaving the 5-10 as visible.

Thanks!

Try it then you would realize the difference

Moreover it gives you flexibility to add or remove visible items by making change in the Named range and need not edit Macro over and over again

 

[BenW71]

Hoping for input from the forum for how to make a number of items visible within a large number of pivot items WITHOUT going through a loop and setting items to visible or not visible.

The code that was previously shown by Sanjay (and thanks for your input sanjay, it just isn't what i'm looking for) does go through the loop and would be the same issue i'm seeing with slowness.

When you filter by the UI (ie not in code) within a pivot table, it is VERY fast even if you are selecting a few items out of a huge pivotitem list in a pivot table. When you do the same execution via code with setting the ones that are not shown to .visible=false, it is very slow.

I also do turn off recalc and redrawing (with application .Calculation=xlCalculationManual, .ScreenUpdating=False, .EnableEvents=False) before i run this...

 

[BenW71]

I think the answer is (partially at least) turning off the pivot calculation via
ActiveSheet.PivotTables("<WHATEVER>").ManualUpdate = True (and back to false after).

Apparently this is separate from application calculation and screen updating.

I did a GetTickCount before and after the filtering and the execution speed doubled when i turned on that toggle.

Are there any other tips on how i can optimize this in addition?

Thanks!