使用 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屋！