MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Pivot table


Posted by Jan on September 18, 2001 6:56 AM

Is there a way to hide rows with "nothing" when you show Pivot Tables?


Posted by Mark W. on September 18, 2001 7:07 AM

Jan is it possible that you've checked the
"Show items with not data" check box on the
PivotTable Field dialog of one of your
PivotTable's fields?

Posted by Jan on September 19, 2001 12:14 AM

No, its not checked!
Though the following semiautomatic macro can be used:

Sub Test()
range = "A3"
field = "Total"
Set pvttable = ActiveSheet.Range(range).PivotTable
For Each PvtField In pvttable.RowFields
For x = 1 To PvtField.PivotItems.Count
If pvttable.GetData(PvtField.PivotItems(x).Name & field) = 0 Then
PvtField.PivotItems(x).Visible = False
End If
Next
Next PvtField
End Sub