how to hide 0 values in pivot table
 

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

how to hide 0 values in pivot table
 

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

how to hide 0 values in pivot table
 

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

how to hide 0 values in pivot table
 

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.


--
steven1001
------------------------------------------------------------------------
steven1001's Profile: http://www.excelforum.com/member.php...o&userid=30757
View this thread: http://www.excelforum.com/showthread...hreadid=528232