Home » Questions » Computers [ Ask a new question ]

Excel 2007 | Remove blank fields from pivot tables

Excel 2007 | Remove blank fields from pivot tables

Every time I create a pivot table (available for all Excel versions) I get one or several blank fields. How can I get rid of them?

Asked by: Guest | Views: 53
Total answers/comments: 2
Guest [Entry]

"When selecting the cells to create the Pivot Table from, don't select any blank rows or columns.


Here's what I came up with in VBA:

Private Sub CommandButton1_Click()

Dim pt As PivotTable
Dim pi As PivotItem

Set pt = ActiveSheet.PivotTables(""PivotTable2"")

For Each pi In pt.PivotFields(""B"").PivotItems
If pi.Value = ""(blank)"" Then pi.Visible = False
Next pi

End Sub

Where ""PivotTable2"" is the name of your Pivot Table, and ""B"" is the name of the Row Field you're trying to eliminate blanks from. You can modify the code to hide other types of fields."
Guest [Entry]

This usually happens if you select blank rows or columns in your source data. Usually for me it happens because I've been lazy and selected whole columns instead of just the area with the data in it.