none
Pivot Table Multi Select Filter Value Performance RRS feed

  • Question

  • I have some really long filters (10k). I have searched the web, and the solutions all seem similar to what I have below. Yet manually choosing multiples from the excel user interface performs exceptionally. When I use single select only, and ".currentpage", I get exceptional performance. When trying to choose multiples (sample below simulates a random seclection). The performance is nothing like what excel is clearly doing. Does anyone know of a way to achieve what Excel does when multi selecting values?

    Sub TestPivot()

        Dim t As Double
        t = Timer()
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual
            Dim pt As PivotTable, ptf As PivotField, pi As PivotItem
        Set pt = ActiveSheet.PivotTables(1)
         pt.ManualUpdate = True
         Set ptf = pt.PivotFields("C1")
        
         ptf.ClearAllFilters

        ptf.EnableMultiplePageItems = True
            
         i = 0
        For Each pi In ptf.PivotItems
                   If InStr(1, pi.Name, "Z") = 0 And InStr(1, pi.Name, "W") = 0 Then
                        pi.Visible = False
               End If
               i = i + 1
        Next
        pt.ManualUpdate = False
            Application.EnableEvents = True
        Application.ScreenUpdating = True
        Application.Calculation = xlCalculationAutomatic
        Debug.Print i, Timer - t
    End Sub

    Thanks

    Adam

    Thursday, November 5, 2015 7:12 PM

Answers

  • Hi Adam,

    >> Does anyone know of a way to achieve what Excel does when multi selecting values?

    Based on your code, it seems you loop the column C1 and check whether it meets your situation. If so, when you loop long filters (10k), I think it is reasonable.

    With Excel Object model, there is no simple way to set multi value filters on a filed. One is like yours to loop the rows one by one. In another way, I suggest you copy the entire Pivot table to the left area, and then filter the new table with multiple filters. A custom filter is not supported in Pivot table, but it is supported in a simple column. After filter the new table, the rows will be filtered too.

    Here is a simple code:

    Sub Macro5()
    '
    ' Macro5 Macro
    '
    
    '
        Range("A3:C8").Select
        Selection.Copy
        Range("E3").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("E3").Select
        Application.CutCopyMode = False
        Selection.AutoFilter
        ActiveSheet.Range("$E$3:$G$8").AutoFilter Field:=1, Criteria1:="=A*", _
            Operator:=xlOr, Criteria2:="=B*"
        Range("N20").Select
    End Sub
    

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, November 6, 2015 3:20 AM

All replies

  • Hi Adam,

    >> Does anyone know of a way to achieve what Excel does when multi selecting values?

    Based on your code, it seems you loop the column C1 and check whether it meets your situation. If so, when you loop long filters (10k), I think it is reasonable.

    With Excel Object model, there is no simple way to set multi value filters on a filed. One is like yours to loop the rows one by one. In another way, I suggest you copy the entire Pivot table to the left area, and then filter the new table with multiple filters. A custom filter is not supported in Pivot table, but it is supported in a simple column. After filter the new table, the rows will be filtered too.

    Here is a simple code:

    Sub Macro5()
    '
    ' Macro5 Macro
    '
    
    '
        Range("A3:C8").Select
        Selection.Copy
        Range("E3").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("E3").Select
        Application.CutCopyMode = False
        Selection.AutoFilter
        ActiveSheet.Range("$E$3:$G$8").AutoFilter Field:=1, Criteria1:="=A*", _
            Operator:=xlOr, Criteria2:="=B*"
        Range("N20").Select
    End Sub
    

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, November 6, 2015 3:20 AM
  • Edward, Thanks for the response. I need to stick with the Pivot as it has all the function we need. There must be a hidden function/process at play with the user interface. As the UI interface performs just as fast with a single filter as it does with a multi select filter (sub second in my case (against 250K of data)), but my best code (as above) takes about 10seconds. The macro recorder generate code (different code), performs even worse.

    Thanks

    Adam

    Friday, November 6, 2015 1:34 PM
  • Hi Adam,

    As in my above reply, there is no directly way to achieve your requirement with Office object model. And we need to loop the each item to set the Visible property just like your code.

    For my workaround, it would not work on Pivot directly, but I think it would filter the Pivot, and then you could delete the backup table and work on your Pivot Table again.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Saturday, November 7, 2015 2:18 AM
  • Edward, I thought I would restart this thread. Is there any progress on surfacing the ability to quickly update multi select values using VBA? maybe .selected=array(of selected values) ??

    For me the issue is more the deselect. That takes the most of time/performance. Even if they linked the currentpage value such that you could toggle to currentpage (select one), then back to multi select, and ONLY have the currentpage selected.

    As it is now currentpage is ignored when you toggle (with VBA) back to multi select.

    Wednesday, March 16, 2016 3:02 PM
  • Hi AuroraMan1,

    Sorry for delay. For this, I would suggest you post a new thread, and share us what you are doing, and what the real questions are. Then there would be more community members to help you.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, March 18, 2016 8:25 AM