使用VBA筛选Excel数据透视表 [英] Filter Excel pivot table using VBA

问题描述

我已经尝试从互联网复制和粘贴解决方案，现在尝试使用VBA在Excel中过滤数据透视表。以下代码不起作用。

I have tried copying and pasting solutions from the internet forever now to try to filter a pivot table in Excel using VBA. The code below doesn't work.

Sub FilterPivotTable()
    Application.ScreenUpdating = False
    ActiveSheet.PivotTables("PivotTable2").ManualUpdate = True
    ActiveSheet.PivotTables("PivotTable2").PivotFields("SavedFamilyCode").CurrentPage = "K123223"
    ActiveSheet.PivotTables("PivotTable2").ManualUpdate = False
    Application.ScreenUpdating = True
End Sub

我想过滤所以我看到所有有SavedFamilyCode K123223的行。我不想在数据透视表中看到任何其他行。我希望这可以工作，不管以前的过滤器。我希望你能帮助我这个。谢谢！

I want to filter so I see all rows that have SavedFamilyCode K123223. I don't want to see any other rows in the pivot table. I want this to work regardless of the previous filters. I hope you can help me with this. Thanks!

根据您的帖子我正在尝试：

Based on your post I'm trying:

Sub FilterPivotField()
    Dim Field As PivotField
    Field = ActiveSheet.PivotTables("PivotTable2").PivotFields("SavedFamilyCode")
    Value = Range("$A$2")
    Application.ScreenUpdating = False
    With Field
        If .Orientation = xlPageField Then
            .CurrentPage = Value
        ElseIf .Orientation = xlRowField Or .Orientation = xlColumnField Then
            Dim i As Long
            On Error Resume Next ' Needed to avoid getting errors when manipulating fields that were deleted from the data source.
            ' Set first item to Visible to avoid getting no visible items while working
            .PivotItems(1).Visible = True
            For i = 2 To Field.PivotItems.Count
                If .PivotItems(i).Name = Value Then _
                    .PivotItems(i).Visible = True Else _
                    .PivotItems(i).Visible = False
            Next i
            If .PivotItems(1).Name = Value Then _
                .PivotItems(1).Visible = True Else _
                .PivotItems(1).Visible = False
        End If
    End With
    Application.ScreenUpdating = True
End Sub

不幸的是我得到运行时间错误91：对象变量或未设置块变量。引起这个错误的是什么？

Unfortunately I get Run time error 91: Object variable or With block variable not set. What has caused this error?

推荐答案

Field.CurrentPage仅适用于过滤器字段（也称为页面字段）。

如果你想过滤一个行/列字段，你必须循环遍历各个项目，如：：

Field.CurrentPage only works for Filter fields (also called page fields).
If you want to filter a row/column field, you have to cycle through the individual items, like so:

Sub FilterPivotField(Field As PivotField, Value)
    Application.ScreenUpdating = False
    With Field
        If .Orientation = xlPageField Then
            .CurrentPage = Value
        ElseIf .Orientation = xlRowField Or .Orientation = xlColumnField Then
            Dim i As Long
            On Error Resume Next ' Needed to avoid getting errors when manipulating PivotItems that were deleted from the data source.
            ' Set first item to Visible to avoid getting no visible items while working
            .PivotItems(1).Visible = True
            For i = 2 To Field.PivotItems.Count
                If .PivotItems(i).Name = Value Then _
                    .PivotItems(i).Visible = True Else _
                    .PivotItems(i).Visible = False
            Next i
            If .PivotItems(1).Name = Value Then _
                .PivotItems(1).Visible = True Else _
                .PivotItems(1).Visible = False
        End If
    End With
    Application.ScreenUpdating = True
End Sub

然后，你会调用：

FilterPivotField ActiveSheet.PivotTables("PivotTable2").PivotFields("SavedFamilyCode"), "K123223"

自然而然，这个字段越慢，个别不同的项目就越慢。如果满足您的需要，还可以使用SourceName而不是Name。

Naturally, this gets slower the more there are individual different items in the field. You can also use SourceName instead of Name if that suits your needs better.

这篇关于使用VBA筛选Excel数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！