数据透视字段显示除空白以外的所有内容 [英] Pivot Field Show All Except Blank

问题描述

刷新后，我需要更新数据透视滤镜以选择除blank以外的所有滤镜选项.
其他答案在这里使用.ShowAllItems方法，该方法对我不起作用，因为它还会显示没有数据的项目(显示下的数据透视选项).

I need to update a pivot filter after refresh to select all filter options except blank.
Other answers here make use of .ShowAllItems method which will not work for me since that will also show items with no data (pivot option under Display).

我尝试使用在另一个问题上找到的另一种解决方案.PivotItems("(All)").Visible = True，然后删除空格，但这无济于事.

I tried to use another solution I found on another question, .PivotItems("(All)").Visible = True and then remove blank but that does not do anything.

如何修改数据透视字段" Activity Name，以检查除空白以外的所有项目?

How can I modify the Pivot Field Activity Name to check every item except blank?

Sub PivotRefresh()

Dim ws As Worksheet
Dim pvt As PivotTable

Application.ScreenUpdating = False
ThisWorkbook.RefreshAll
Application.Calculation = xlCalculationManual
    For Each ws In Worksheets
        If ws.Name = "OC" Or ws.Name = "P2" Then
            For Each pvt In ws.PivotTables
                On Error Resume Next
                    pvt.PivotFields("Exclude").PivotItems("Yes").Visible = False
                    pvt.PivotFields("Activity Name").PivotItems("(All)").Visible = True
                    pvt.PivotFields("Activity Name").PivotItems("(blank)").Visible = False
                On Error GoTo 0
            Next pvt
        End If
        ws.Range("A:W").EntireColumn.AutoFit
    Next ws
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

推荐答案

当我必须这样做时，我只会使用类似的东西:

When I have to do this, I just use something like:

On Error Resume Next
With pvt
        .ClearAllFilters
        .PivotItems("(blank)").Visible = False
End With

这篇关于数据透视字段显示除空白以外的所有内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！