CODEWITHSUNDEEP
excelvbafilterpivot-table
Matheus Vassoler
Matheus Vassoler

Reputation: 15

How to filter a filter in a pivot table

I'm trying to filter a filter in a pivot table in excel using VBA, but the process is spending a lot of time. The user type in a text box and click in the submit button to start the operation. My filter has more than 2.000 values. This is the code that I use in this situation.

Does it exist a fastest way to filter?

Sub a()
Dim txt1 As String

txt1 = Worksheets("Planilha1").TextBox1.Value

If Not txt1 = "" Then
    Set ws = Sheets("Planilha1")
    Set pt = ws.PivotTables(1)
    Set pf = pt.PageFields(1)

    For Each Pi In pf.PivotItems
      If Not Pi = txt1 Then
        pf.PivotItems(CStr(Pi)).Visible = False
      End If
    Next Pi
End If
End Sub

Upvotes: 0

Views: 171

Answers (1)

Tim Williams
Tim Williams

Reputation: 166306

This is significantly faster:

Sub a()
    Dim txt1 As String, ws As Worksheet, pt As PivotTable, pf As PivotField, pi As PivotItem
    Dim t


    txt1 = "Var_1099"

    If Not txt1 = "" Then
        Set ws = ActiveSheet
        Set pt = ws.PivotTables(1)
        Set pf = pt.PivotFields("Col1")

        t = Timer

        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual
        pt.ManualUpdate = True

        For Each pi In pf.PivotItems
            pi.Visible = (pi = txt1)
        Next pi

        Application.Calculation = xlCalculationAutomatic
        pt.ManualUpdate = False
        pt.Update

        Debug.Print Timer - t
    End If

End Sub

Upvotes: 2

Related Questions