• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Selecting Multiple Slicer Items

P

patsfan

Member
Guys,

I have a pivot slicer listing 52 different week date filters.
There are some time filter options to provide users with specific totals. "Last 52 Weeks", "Last 26 Weeks", "Last 13 Weeks", "Month to Date", etc.

When a time filter is changed, it is taking about 1.5 minutes to return results (large data set). All I can see are two options in VBA to consider efficient functionality.

One option is to loop through each slicer item and select the weeks applicable to the time filter. However, after each week is selected (using the VBA loop) the pivot updates (takes about 2 seconds each) so the entire process takes far too long to complete. I cannot seem to find a way to stop the pivot updates during the loop process. I've used many different code suggestions to disable the PT update but none of them seemed to work for me. Enable Events=False, Calculation=False, Calculation=xlCalculationManual, ManualUpdate=False.

Another option is to select multiple slicer items at the same time using some type of array along with the slicer item location (1), (2), (3), etc. However, I cannot seem to find any VBA array code that will allow me to use this option.

Can anyone advise of a different option and/or an example of a VBA array that would change multiple slicer items at the same time?

Thanks
 
There are several methods. But what method is best suited to your need will depend on how your pivot table is constructed.

Largest difference is... Is your pivot table based on Data Model or based on regular Excel Range/Table (traditional)?

If former, you'd use .VisualItemsList array to select multiple. You should find some sample codes in this forum.

If latter, you'd set PivotTableObject.ManualUpdate to true before you initiate loop, and then turn it back to false at end of code.
 
Thanks Chihiro, I used the latter and it cut the loop time in half. I also saved some time by exiting the loop when all remaining conditions allowed me to do so. However, for certain loops, it still takes about 30 seconds to loop through the slicer items.

I was unable to piece together code for an array which would change multiple slicer items with a single keystroke. Can you, or someone, provide me with sample array code to change slicer items (1), (2), and (3) simultaneously? (if that is even an option for me)
The slicer cache is named "Slicer_Week3" if that helps.
Thanks
 
You must log in or register to reply here.
Back
Top