VBA loop through Pivot fields and change number format of specific cells based on 1 criteria-CodePudding

I am trying to change the (blank) cells in a handful of pivot tables on the activesheet. What I thought was a simple code is giving me more problems than I thought.

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

CodePudding user response：

You need to loop through the PivotFields collection and then loop though the PivotItems collection. The PivotField is like a column of the table, and the PivotItem is the individual cell. PivotFields don't have values, they have a collection of PivotItems and each PivotItem has a value.

Other than that, there is a second problem. PivotItems are not explicitly tied to cells of a pivot table. For example, if they are hidden by use of a filter, then they have no associated cell on the worksheet despite being data currently tied to the PivotField. So you cannot do PivotItem.NumberFormat because they are not a Range. Each pivot item can give you its PivotItem.LabelRange which will tell you where it is located on the worksheet if it is currently located on the worksheet.

But if it is hidden PivotItem.LabelRange gives an error instead of Nothing... So to test if the PivotItem currently has a range, you need to skip errors, ask for LabelRange and then test if the result is a valid range.

After getting a valid range, you can set .NumberFormat as desired.

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