Pivot tables

[Don Niall]

Hi,

When creating Pivot tables is there a way to suppress all
data that has zero grand total values (if summarising
using SUM).

Thx,

Don-

 

[John P]

Goto tools options and uncheck the zeros option.

John

 

[Don Niall]

John,

Thanks for the reply. I was hoping it might remove the
entire pivot-table record for those grand totals that are
zero? While it does remove the zero's the record remains -
so essentially the pivot table remains the same size ....

Don-

 

[Debra Dalgleish]

The following code will hide items with a zero row total, in Excel 2002
or later version:

'================================
Sub HideZeroItemTotals()
'hide rows that contain zero totals
Dim r As Integer
Dim i As Integer
Dim pt As PivotTable
Dim pf As PivotField
Dim df As PivotField
Dim pi As PivotItem
Dim pd As Range
Dim str As String
Set pt = Sheets("Pivot").PivotTables(1)
Set df = pt.PivotFields("Total") 'data field
Set pf = pt.PivotFields("Rep") 'row field

For Each pi In pf.PivotItems
pi.Visible = True
Next pi

i = pf.PivotItems.Count
For r = i To 1 Step -1
On Error Resume Next
str = Cells(r + 5, 1).Value
Set pd = pt.GetPivotData(df.Value, pf.Value, str)
If pd.Value = 0 Then
pf.PivotItems(str).Visible = False
End If
Next r

End Sub
'========================