excel - 使用 VBA 选择数据透视表中的最后 3 个值(周)，但不包含 'blank'

Question

我有一个包含多个数据透视表的报告。

我发现下面的宏在一个数据透视表中选择所需的周数(在我的情况下，我需要始终选择最后 3 周)。它确实选择了最后 3 个值，但问题是，它还选择了“空白”字段。

代码如下:

Sub ShowLastXWeeks()
Dim pi As PivotItem
Dim lLoop As Long
Dim pt As PivotTable
Dim lCount As Long
Dim lWeeks As Long

On Error Resume Next
lWeeks = 3
If lWeeks = 0 Then Exit Sub

Application.ScreenUpdating = False
Set pt = ActiveSheet.PivotTables("PivotTable1")

        For Each pi In pt.PivotFields("Week").PivotItems
            pi.Visible = False
        Next pi


    With pt.PivotFields("Week")
        For lLoop = .PivotItems.Count To 1 Step -1
            .PivotItems(lLoop).Visible = True
            lCount = lCount + 1
            If lCount = lWeeks Then Exit For
        Next lLoop
    End With

On Error GoTo 0
Application.ScreenUpdating = True
End Sub

我的数据透视表如下:

我试图摆脱“空白”值，但没有成功。

如何修改上述脚本以省略“空白”字段并仅选择最近 3 周？

提前致谢。

Answer 1

我已经找到了解决我自己问题的方法，所以我希望我能帮助其他遇到同样问题的人。我通过将以下内容添加到我的代码中摆脱了“空白”值:

Set pf = pt.PivotFields("Week")
With pf
On Error Resume Next
    .PivotItems("(blank)").Visible = False
On Error GoTo 0
End With

整个代码如下:

Sub ShowLastXWeeks()
Dim pi As PivotItem
Dim lLoop As Long
Dim pt As PivotTable
Dim lCount As Long
Dim lWeeks As Long

On Error Resume Next
lWeeks = 4
If lWeeks = 0 Then Exit Sub

Application.ScreenUpdating = False
Set pt = ActiveSheet.PivotTables("PivotTable1")

    For Each pi In pt.PivotFields("Week").PivotItems
        pi.Visible = False
    Next pi


With pt.PivotFields("Week")
    For lLoop = .PivotItems.Count To 1 Step -1
        .PivotItems(lLoop).Visible = True
        lCount = lCount + 1
        If lCount = lWeeks Then Exit For
    Next lLoop
End With

On Error GoTo 0

Set pf = pt.PivotFields("Week")
With pf
On Error Resume Next
    .PivotItems("(blank)").Visible = False
On Error GoTo 0
End With

Application.ScreenUpdating = True

End Sub

谢谢