empty cells in Print Tab

[sathis85]

My source data has empty cells. When I PT this data - the PT shows "(blank)" for the empty cells. How can I keep the cells "empty" in the PT. I do not want to manually filter and uncheck (blank) as this removes other data which is needed.

Kindly find the below file

https://dl.dropbox.com/u/97330471/PIVOT%20BLANKS.xlsx


Thanks

 

[Hui]

Sathis85


In one line you say "How can I keep the cells empty"

and then you say "I do not want to manually filter and uncheck (blank)"


Can you clarify what you want? as I'm confused

 

[sathis85]

Hi Hui,


Thanks for the reply,,..................


Actually i don't want "Blanks" in my PT and every time i refresh it could not appear also. pls help me to find a right solution


Thank u ,,,,,,,,

 

[Hui]

Try some VBA code

Select the following

Paste it into the worksheet object in VBA "Sheet1"


It should work when the data changes and you update the PT

[pre]

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Region")
.PivotItems("(blank)").Visible = False
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Creation date")
.PivotItems("(blank)").Visible = False
End With

With ActiveSheet.PivotTables("PivotTable4").PivotFields("Region")
.PivotItems("(blank)").Visible = False
End With
With ActiveSheet.PivotTables("PivotTable4").PivotFields("Creation date")
.PivotItems("(blank)").Visible = False
End With

End Sub

[/pre]