attempting to speed up pivot table filter selection

ajm · Sep 19, 2011

Hi folks, i keep getting errors with the second of the two macros below while PT.ManualUpdate=True is used. I am trying to speed up the filter selection process which looks at the value selected in a combo box and if that value does not equal the pivot item value it hides the pivot item. it loops through all pivot items. without the PT.ManualUPdate bit, the second macro delivers the correct result - it just takes forever and a day...

Sub ChooseMAM() ****Dim critRange As Range, c As Range, ws As Worksheet, PT As PivotTable, pi As PivotItem ****Set critRange = Worksheets("Annual Results").Range("C2") ****Dim strFields As String, strFields2 As String ****Dim graphSheets As Variant ****strFields = Worksheets("Annual Results").Range("b2").Value ****strFields2 = "Debtor Normal Name" ****PT.ManualUpdate = True ****strValue = critRange.Value ****Worksheets("Annual Results").Range("D3").Value = "0" ****Set graphSheets = Sheets(Array("Rep Sales Data", "TY Sales Data")) ****For Each ws In graphSheets ********For Each PT In ws.PivotTables ************With PT.PivotFields(strFields2) ****************.ClearAllFilters ************End With ************With PT.PivotFields(strFields) ****************.ClearAllFilters ****************For Each pi In PT.PivotFields(strFields).PivotItems ********************If pi.Value = strValue Then ************************.CurrentPage = strValue ************************Exit For ********************Else ************************.CurrentPage = "(All)" ********************End If ****************Next pi ************End With ********Next PT ****Next ws ****PT.ManualUpdate = False End Sub Sub ChooseAccount() ****Dim AccountRange As Range, c As Range, ws As Worksheet, PT As PivotTable, pi As PivotItem ****Dim strDebtorName As String, strFields2 As String ****Dim graphSheets As Variant ****Dim j As Long ****strFields2 = "Debtor Normal Name" ****Set AccountRange = Worksheets("Annual Results").Range("c3") ****strDNValue = AccountRange.Value ****PT.ManualUpdate = True ****Set graphSheets = Sheets(Array("Rep Sales Data", "TY Sales Data")) ****For Each ws In graphSheets ********For Each PT In ws.PivotTables ************With PT.PivotFields(strFields2) ****************For Each pi In PT.PivotFields(strFields2).PivotItems ********************For j = 1 To .PivotItems.Count ************************If .PivotItems(j) <> strDNValue And _ ************************** .PivotItems(j).Visible = True Then ****************************.PivotItems(j).Visible = False ************************End If ********************Next j ****************Next pi ************End With ********Next PT ****Next ws ****PT.ManualUpdate = False End Sub

the macros are an amalgam of other macros so there should be some cleanups to do still, so any suggestions in that direction would also be appreciated.

Jerry Sullivan · Sep 20, 2011

Hi ajm,

In both functions you posted, the PT.ManualUpdate statements are outside of the For Each PT block. Try moving them inside.

Rich (BB code):

For Each ws In graphSheets
    For Each PT In ws.PivotTables
        PT.ManualUpdate = True
        With PT.PivotFields(strFields2)
            For Each pi In PT.PivotFields(strFields2).PivotItems

                For j = 1 To .PivotItems.Count
                    If .PivotItems(j) <> strDNValue And _
                       .PivotItems(j).Visible = True Then
                        .PivotItems(j).Visible = False
                    End If
                Next j
            Next pi
        End With
        PT.ManualUpdate = False
    Next PT
Next ws

I'm not sure whether this will speed your code up to an acceptable level, but it should help.
If that doesn't fix things, please provide How many PivotItems are in Debtor field? How long is it taking to run?

ajm · Sep 21, 2011

hi jerry, i have moved the lines as you suggested and am running it now. it doesn't appear to have made any difference to the time it takes to run. anywhere from 2 - 5 seconds for the first combo list (23 items) and 235 seconds for the second combo list (423 items). The code you supplied me with originally (http://www.mrexcel.com/forum/showthread.php?t=575835&page=2) works better than this, i just couldn't get it to clear the second validation list.

where abouts did you get to on your trip over here?

Biz · Sep 21, 2011

Hi,

Try

Code:

'Start of your code
' Speeding Up VBA Code
    With Application
        .ScreenUpdating = False 'Prevent screen flickering
        .Calculation = xlCalculationManual 'Preventing calculation
        .DisplayAlerts = False 'Turn OFF alerts
        .EnableEvents = False 'Prevent All Events
    End With
 
' Start your code here
 
 
'End of your code
'Speeding Up VBA Code
    With Application
        .ScreenUpdating = True 'Prevent screen flickering
        .Calculation = xlAutomatic 'Preventing calculation
        .DisplayAlerts = True 'Turn OFF alerts
        .EnableEvents = True 'Prevent All Events
    End With

Is it faster now?

Biz

ajm · Sep 21, 2011

biz, it definitely is quicker. i know there are a lot of pivot items to compare and hide (423) but it still takes 35 seconds. I am going to run with this for this afternoon and promise further enhancements, improvements, bells and whistles, etc...

Biz · Sep 21, 2011

ajm said:
biz, it definitely is quicker. i know there are a lot of pivot items to compare and hide (423) but it still takes 35 seconds. I am going to run with this for this afternoon and promise further enhancements, improvements, bells and whistles, etc...

I am thinking using vba arrays processing maybe faster but I am not very strong with it. Loops are slower. I'm sure someone can come with better code.

Biz

AlphaFrog · Sep 21, 2011

This may help as well.

 For Each PT In ws.PivotTables ' Set update to manual to avoid recomputation while making changes PT.ManualUpdate = True With PT.PivotFields(strFields2) For Each Pi In PT.PivotFields(strFields2).PivotItems For j = 1 To .PivotItems.Count If .PivotItems(j) <> strDNValue And _ .PivotItems(j).Visible = True Then .PivotItems(j).Visible = False End If Next j Next Pi End With PT.ManualUpdate = False Next PT

EDIT: Sorry, just noticed JS411's post.

Jerry Sullivan · Sep 21, 2011

Hi guys,

Part of the reason that the code checks the Visible state of each PivotItem before changing it,
is that when you have a large number of PivotItems, there is a huge time savings in only changing
the state of the ones that need to be changed.

So if you have 1 item visible and you want to change to 1 different visible item,
this is pretty fast...

Rich (BB code):

For j = 1 To .PivotItems.Count
    If .PivotItems(j) <> strDNValue And _
       .PivotItems(j).Visible = True Then
        .PivotItems(j).Visible = False
    End If
Next j

This is much slower...even though in both cases you are only changing the state of 2 items.

Rich (BB code):

For j = 1 To .PivotItems.Count
    If .PivotItems(j) <> strDNValue Then
        .PivotItems(j).Visible = False
    End If
Next j

I think the reason the code I suggested in the earlier thread worked faster, is that it was always leaving only one PivotItem Visible.
In your current process, my understanding is that you are making all PivotItems visible each time you change an Account Manager.
That makes the speed comparable to the second code snippet instead of the first.

You might want to revisit that approach and have a separate "showall" macro for those cases that you want to see all Debtors.

ajm said:
where abouts did you get to on your trip over here?

We visited Sidney, Ayers Rock, Alice Springs and Cairns. Wonderful people and places!

ajm · Sep 21, 2011

JS411 said:
We visited Sidney, Ayers Rock, Alice Springs and Cairns. Wonderful people and places!

everybody forgets brisbane... boo hoo Jerry, isn't Sydney Harbour the most captivating puddle you have ever seen?! I used to sail there every saturday afternoon prior to us moving back to brisbane before the kids were born. my wife doesn't understand my pain....

I will have a crack at a "show all" macro later this afternoon. what I have at present will get me across the line and then i will have more time after presentation to tidy things up. I'll keep this thread open until i have tied this one down.

Jerry Sullivan · Sep 22, 2011

Replace the block with this and it will be a wee bit faster.
(423 times faster in your case).

Code:

    For Each ws In graphSheets
        For Each PT In ws.PivotTables
            PT.ManualUpdate = True
            With PT.PivotFields(strFields2)
               .PivotItems(strDNValue).Visible = True
                For j = 1 To .PivotItems.Count
                    If .PivotItems(j) <> strDNValue And _
                       .PivotItems(j).Visible = True Then
                        .PivotItems(j).Visible = False
                    End If
                Next j
            End With
            PT.ManualUpdate = False
        Next PT
   Next ws

attempting to speed up pivot table filter selection

ajm

Well-known Member

Excel Facts

Jerry Sullivan

MrExcel MVP

ajm

Well-known Member

Biz

Well-known Member

ajm

Well-known Member

Biz

Well-known Member

AlphaFrog

MrExcel MVP

Jerry Sullivan

MrExcel MVP

ajm

Well-known Member

Jerry Sullivan

MrExcel MVP

Similar threads

Forum statistics

Share this page

attempting to speed up pivot table filter selection

Well-known Member

Excel Facts

MrExcel MVP

Well-known Member

Well-known Member

Well-known Member

Well-known Member

MrExcel MVP

MrExcel MVP

Well-known Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock