none
Pivot Table - PageField - Multi Select Filter Performance RRS feed

  • Question

  • User interface performance is exponentially faster than VBA when choosing multiple values.

    I am attaching a simple Pivot that illustrates the issue.

    Using the UI, add a filter for *777 in the multi select window, then press enter (takes about a second)

    Using VBA , just to do 2000 of the 20K records takes 13s.

    Recording a macro yields a mess of hardcoded selections.

    Thanks

    Adam

    file in dropbox

    Source data, is 20,000 records using formula

    COL F1="A" & RIGHT("00000000"&ROW(A1),7)

    COL F2==ROW(A1)

    then build a pivot, place F1 in Pagefilter and f2 in value (change to count)

    Sub Test()
        t1 = Timer
        Dim pt As PivotTable
        Set pt = Sheets("pivot").PivotTables(1)
        Dim pf As PivotField
        Set pf = pt.PivotFields("F1")
        Dim itm As PivotItem
        Debug.Print pf.PivotItems.Count
        pt.ManualUpdate = True
        With Application
                    .ScreenUpdating = False
                    .EnableEvents = False
                    .Calculation = xlCalculationManual
        End With
        With pf
            .ClearAllFilters
            Debug.Print pt.ManualUpdate
            pt.ManualUpdate = True
            Debug.Print pt.ManualUpdate
            For i = 1 To 2000 '.PivotItems.Count
                Set itm = .PivotItems(i)
                If Right(itm.Name, 3) = "777" Then
                    itm.Visible = True
                Else
                    itm.Visible = False
                End If
            Next
        End With
            With Application
                    .ScreenUpdating = True
                    .EnableEvents = True
                    .Calculation = xlCalculationAutomatic
        End With
        pt.ManualUpdate = False
        Application.ScreenUpdating = True
        Debug.Print Timer - t1

    End Sub

    Monday, April 25, 2016 7:16 PM

Answers

  • >>>User interface performance is exponentially faster than VBA when choosing multiple values.

    According to your description, I have downloaded your sample file and reproduce this issue. Unfortunately, I am not able to find any clue to overcome this issue.

    So I suggest that you could submit any feedback for Excel to User Voice:

    https://excel.uservoice.com/

    Thanks for your understanding.
    • Proposed as answer by David_JunFeng Wednesday, May 4, 2016 8:39 AM
    • Marked as answer by David_JunFeng Thursday, May 5, 2016 9:57 AM
    Wednesday, April 27, 2016 6:49 AM