VBA循环遍历透视字段并根据1个条件更改特定单元格的数字格式

Question

我正在尝试更改活动工作表上几个数据透视表中的(空白)单元格。我认为是一个简单的代码，给我带来的问题比我想象的要多。

Sub HideBlank()
Dim pvt As PivotTable
Dim P_i As PivotField
For Each pvt In ActiveSheet.PivotTables
Debug.Print pvt.Name
For Each P_i In pvt.PivotFields
If P_i.Value = "(blank)" Then
P_i.NumberFormat = ";;;"
End If
Next
Next
End Sub

Answer 1

您需要遍历 PivotFields 集合，然后遍历 PivotItems 集合。数据透视字段类似于表的一列，而数据透视项是单个单元格。数据透视字段没有值，它们具有数据透视项的集合，并且每个数据透视项都有一个值。

除此之外，还有第二个问题。数据透视项未显式绑定到数据透视表的单元格。例如，如果它们通过使用筛选器隐藏，则尽管它们是当前绑定到数据透视字段的数据，但它们在工作表上没有关联的单元格。所以你不能做PivotItem.NumberFormat因为他们不是Range.每个透视表项都可以为您提供其PivotItem.LabelRange如果它当前位于工作表上，它将告诉您它在工作表上的位置。你可以做PivotItem.LabelRange.NumberFormat，但LabelRange并不总是存在的。

如果数据透视项是隐藏的，PivotItem.LabelRange则给出错误而不是Range。因此，要测试PivotItem当前是否有Range，您需要跳过错误，要求LabelRange，然后测试结果是否有效Range。

获得有效范围后，您可以根据需要设置.NumberFormat。

Sub HideBlank()
Dim pvt As PivotTable
Dim P_f As PivotField
Dim P_i As PivotItem
Dim ItemRange As Range

For Each pvt In ActiveSheet.PivotTables
Debug.Print pvt.Name
For Each P_f In pvt.PivotFields
For Each P_i In P_f.PivotItems
If P_i.Visible And P_i.Value = "(blank)" Then
Set ItemRange = Nothing
On Error Resume Next
Set ItemRange = P_i.LabelRange
On Error GoTo 0
If Not ItemRange Is Nothing Then
ItemRange.NumberFormat = ";;;"
End If
End If
Next
Next
Next
End Sub