Selecting Multiple PivotItems - Slow

Peter.Stevens2

Board Regular
Joined
Sep 16, 2008
Messages
56
Hi Folks,

I'm having a nightmare trying to speed up some code I've written to select multiple page fields in Excel 2007 Pivot tables.

I have a workbook that has several sheets with 20+ pivottables on each that make up a dashboard of sorts, there is a user form that users can use to select filters on these pivot tables and the selected filters are used to apply to all pivot tables on the active worksheet. this saves them having to make the selections on each pivot table in turn.

I'd set this up so users could select 'all' pivot items in a partcular field or just select one, for a specific team etc. This works fine and refreshes the filter on all pivot tables in a couple of seconds. However I've had a request to make some of the pivot item selections 'multiselect'. I've done this by changing the Combobox on the userform into a multiselect Listbox which populates an array when the user apply's their selections. The Array is then looped through to make the pivot items visible. the problem is that the Pivot fields in question can have up to 300ish indivudual items and it takes aaaaaages for the loop to go through each of these pivot items and make it Visible/Hidden as applicable. I've tried using pt.ManualUpdate = True to no avail.

The source data for the pivots are in the same workbook and all pivot tables run off 1 or 2 pivot caches.

The code I'm having trouble with is below, any help to fix would be greatly appreciated! Thanks All

Code:
'loop through each pivottable and make correct page selections
For Each pt In ThisWorkbook.ActiveSheet.PivotTables
    pt.ManualUpdate = True
 
    Application.StatusBar = "Calculating - " & Round(Counter / ptCounter, 2) * 100 & "%"
 
    'if there are selected accounts then apply the filter
    If FilterAccount = True Then
 
        pt.PivotFields("Account").EnableMultiplePageItems = True
 
        For Each pi In pt.PivotFields("Account").PivotItems
            If pi <> "(blank)" Then pi.Visible = False
        Next pi
 
        For i = 1 To UBound(AccountArray, 1)
            pt.PivotFields("Account").PivotItems(AccountArray(i)).Visible = True
        Next i
 
        pt.PivotFields("Position").EnableMultiplePageItems = True
 
    'else select all accounts
    Else
 
        pt.PivotFields("Account").ClearAllFilters
        pt.PivotFields("Account").EnableMultiplePageItems = False
        pt.PivotFields("Account").CurrentPage = "(All)"
 
    End If
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Hi Peter,

You can greatly reduce the time needed to filter for multiple PivotItems by testing whether each item is Visible first and only changing its state if it's currently incorrect.

There's a Function "Filter_PivotField" in this thread shows one way to do that.
The example in that thread is for setting one Page; however the Function is written to handle one or more PivotItems.

http://www.mrexcel.com/forum/showpost.php?p=2952507&postcount=2
 
Upvote 0
Thanks Jerry, your function reduced the run time of the sub routine down from over 700 seconds to a more manageable 450ish.

Alas I think it's the sheer number of Tables, Fields and Items that need to be checked that is causing the routine to take so long. I think I'm going to leave it at that as this will only be a problem when a user chooses all of the filter options available, this will only be about 5% of the time. I've put a warning box when the problematic filters are selected to warn the user that the routine may take some time to run!

I appreciate your help!

Pete
 
Upvote 0

Forum statistics

Threads
1,211,456
Messages
6,101,954
Members
447,765
Latest member
bhutta5437

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top