CODEWITHSUNDEEP
vbaexcel
David Shochet
David Shochet

Reputation: 5395

Excel VBA: How to show blanks as 0 in pivot table?

I am creating a macro which creates a pivot table with counts. I noticed that in case the count is zero, it is displayed as blank. Is there a way to make it 0 programmatically instead?

Here is my code:

With Worksheets("Working")

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="'" & rngData.Worksheet.Name & "'!" & rngData.Address(ReferenceStyle:=xlR1C1), _
        Version:=xlPivotTableVersion12).CreatePivotTable TableDestination:=ws.Range("A1"), _
        TableName:="HDPivotTable", DefaultVersion:=xlPivotTableVersion12
End With

ws.PivotTables("HDPivotTable").AddFields RowFields:="Row Label"

ws.PivotTables("HDPivotTable").PivotFields("h,d,x").Orientation = xlDataField

With ws.PivotTables("HDPivotTable").PivotFields("h,d,x")
    .Orientation = xlColumnField
    ws.PivotTables("HDPivotTable").PivotCache.MissingItemsLimit = xlMissingItemsNone
    ws.PivotTables("HDPivotTable").PivotCache.Refresh

    For i = 1 To .PivotItems.Count
        ' Filter out columns that are not "D" or "H"
        If .PivotItems(i) <> "D" And .PivotItems(i) <> "H" Then
            .PivotItems(i).Visible = False
        End If
    Next
End With

Thanks.

Upvotes: 2

Views: 1255

Answers (1)

luke_t
luke_t

Reputation: 2985

You should be able to use PivotTable.NullString:

ws.PivotTables("HDPivotTable").NullString = "0"

Upvotes: 3

Related Questions