how to hide 0 values in pivot table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 2 fields in the row area - Customer as primary grouping and Segment as
secondary group. There are many fields in the data area, but one is 2005 Vol.
I would like to hide all the rows with 0 values.

I've tried this code, which works if I only have one row field but since
there are 2 row fields, no rows are hidden.

Sub HideZeroRowTotals()
'hide rows that contain zero totals
'by Debra Dalgleish
Dim r As Integer
Dim rTop 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("Summary Pivot").PivotTables(1)
Set df = pt.PivotFields("2005 Vol") 'data field
Set pf = pt.PivotFields("Customer") 'column field
rTop = 7 'number of rows before data starts
For Each pi In pf.PivotItems
On Error Resume Next
pi.Visible = True
Next pi
i = pf.PivotItems.Count + rTop
For r = i To rTop - 1 Step -1
On Error Resume Next
str = Cells(r, 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


How do I modify this to recognize the 2 different levels of grouping? Thank
you!!
 
what about doing a conditional formatting, if cell value is zero change the
font color to background color( Example White). this way you won't see the
zeros.

Nikki
 
Yes, that would hide the values, but I'd really like to hide the entire row.
It's a large pivot table and there are quite a few groups with no values.
Thanks for the suggestion, though.
 
An alternative may be to use 'sumif' and check for which values sum t
zero, set a label in a helper column to "do not display" if zero els
"display". Put the label in the PAGE area and filter to select only th
'display' items
 
Back
Top