2

I have a script that automates setting some filters on a pivot table depending on the data (hiding and showing certain articles). Now the script works great but when I have a pivot chart it gets super slow. I think what's happening in the background is that the chart is re-calculated for every article I show/hide.

Is there a way to set multiple filters without updating the table and then update the pivot chart & table once they are all set?

I thought about deleting the chart, setting the filters and then re-creating the chart. But this would remove any formatting I set on the chart so I would prefer not to go that path.

Sub btnVar() ' function to show only articles with variable data
    onlyVar (True)
End Sub

Sub btnConst() ' function to show only articles with constant data
    onlyVar (False)
End Sub

Sub onlyVar(check As Boolean)
    calcOff 'disable screen updates etc

'first show all articles 
ActiveSheet.PivotTables("PivotTable1").PivotFields("Artikel").ClearAllFilters

    Dim dict As Dictionary
    Set dict = New Dictionary
    art = 9 'start from column 9
    article = Sheet4.Cells(3, art).Value 'first article name
    lrow = Cells(Rows.Count, art).End(xlUp).Row 'find last row

' set article visibility in dictionary according to desired filter (constant/variable
    Do While article <> "" 'loop over article names until end of table
        If Sheet4.Cells(4, art).Value = Application.Average(Range(Cells(4, art), Cells(lrow, art))) Then
            dict.Add article, Not check 
        Else
            dict.Add article, check
        End If
        art = art + 1
        article = Sheet4.Cells(3, art).Value
    Loop

' set the filters, this causes the table & chart to update every time a filter is set.
' it is fine for table only but with chart it gets super slow.

    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Artikel")
        For Each ptitm In .PivotItems
            ptitm.Visible = dict.Item(ptitm.Name)
        Next
    End With
    calcOn 're-enable screen updates etc
End Sub

Public Sub calcOff()
    Application.Calculation = xlCalculationManual
    Application.DisplayStatusBar = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False
End Sub

Public Sub calcOn()
    Application.Calculation = xlCalculationAutomatic
    Application.DisplayStatusBar = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Any help to make this faster would be appreciated. Thanks in advance!

superuser
  • 125
  • 7
  • Just an idea: did you try `Application.EnableEvents = False` ? – iDevlop Jun 21 '19 at 08:33
  • Hi :) If you scroll down, in my `calcOff` sub, I disable caluclations, status bar updates, events and screen updates. But thanks for the hint! – superuser Jun 21 '19 at 12:55
  • I saw that but EnableEvents is a different thing. Did you try it ? I was hoping it could prevent the chart to be recalculated – iDevlop Jun 21 '19 at 13:17
  • I think I've got your solution: https://stackoverflow.com/a/38663498/78522 – iDevlop Jun 21 '19 at 13:19
  • Did you check my code? I literally have the exact same line you are suggesting :) `Application.EnableEvents = False` – superuser Jun 21 '19 at 13:31

0 Answers0