how to hide 0 values in pivot table

maryj · 03-30-2006, 03:10 PM

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!!

--
maryj

Nikki · 03-30-2006, 08:45 PM

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

"maryj" wrote:

> 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!!
>
> --
> maryj

maryj · 03-31-2006, 09:10 AM

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.
--
maryj

"Nikki" wrote:

> 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
>
> "maryj" wrote:
>
> > 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!!
> >
> > --
> > maryj

**steven1001** · 04-01-2006, 04:49 AM

An alternative may be to use 'sumif' and check for which values sum to zero, set a label in a helper column to "do not display" if zero else "display". Put the label in the PAGE area and filter to select only the 'display' items.