excel - 如何过滤数据透视表中的过滤器

Question

我正在尝试使用 VBA 在 excel 中的数据透视表中过滤过滤器，但该过程花费了大量时间。用户在文本框中键入并单击提交按钮以开始操作。我的过滤器有超过 2.000 个值。这是我在这种情况下使用的代码。

是否存在最快的过滤方式？

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

标签： excelvbafilterpivot-table

Answer 1

这明显更快：

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