software

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(quot;Summary Pivotquot;).PivotTables(1)
Set df = pt.PivotFields(quot;2005 Volquot;) 'data field
Set pf = pt.PivotFields(quot;Customerquot;) '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 subHow do I modify this to recognize the 2 different levels of grouping? Thank
you!!

--
maryj

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

quot;maryjquot; wrote:

gt; I have 2 fields in the row area - Customer as primary grouping and Segment as
gt; secondary group. There are many fields in the data area, but one is 2005 Vol.
gt; I would like to hide all the rows with 0 values.
gt;
gt; I've tried this code, which works if I only have one row field but since
gt; there are 2 row fields, no rows are hidden.
gt;
gt; Sub HideZeroRowTotals()
gt; 'hide rows that contain zero totals
gt; 'by Debra Dalgleish
gt; Dim r As Integer
gt; Dim rTop As Integer
gt; Dim i As Integer
gt; Dim pt As PivotTable
gt; Dim pf As PivotField
gt; Dim df As PivotField
gt; Dim pi As PivotItem
gt; Dim pd As Range
gt; Dim str As String
gt; Set pt = Sheets(quot;Summary Pivotquot;).PivotTables(1)
gt; Set df = pt.PivotFields(quot;2005 Volquot;) 'data field
gt; Set pf = pt.PivotFields(quot;Customerquot;) 'column field
gt; rTop = 7 'number of rows before data starts
gt; For Each pi In pf.PivotItems
gt; On Error Resume Next
gt; pi.Visible = True
gt; Next pi
gt; i = pf.PivotItems.Count rTop
gt; For r = i To rTop - 1 Step -1
gt; On Error Resume Next
gt; str = Cells(r, 1).Value
gt; Set pd = pt.GetPivotData(df.Value, pf.Value, str)
gt; If pd.Value = 0 Then
gt; pf.PivotItems(str).Visible = False
gt; End If
gt; Next r
gt; End sub
gt;
gt;
gt; How do I modify this to recognize the 2 different levels of grouping? Thank
gt; you!!
gt;
gt; --
gt; maryj

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.
--
maryjquot;Nikkiquot; wrote:

gt; what about doing a conditional formatting, if cell value is zero change the
gt; font color to background color( Example White). this way you won't see the
gt; zeros.
gt;
gt; Nikki
gt;
gt; quot;maryjquot; wrote:
gt;
gt; gt; I have 2 fields in the row area - Customer as primary grouping and Segment as
gt; gt; secondary group. There are many fields in the data area, but one is 2005 Vol.
gt; gt; I would like to hide all the rows with 0 values.
gt; gt;
gt; gt; I've tried this code, which works if I only have one row field but since
gt; gt; there are 2 row fields, no rows are hidden.
gt; gt;
gt; gt; Sub HideZeroRowTotals()
gt; gt; 'hide rows that contain zero totals
gt; gt; 'by Debra Dalgleish
gt; gt; Dim r As Integer
gt; gt; Dim rTop As Integer
gt; gt; Dim i As Integer
gt; gt; Dim pt As PivotTable
gt; gt; Dim pf As PivotField
gt; gt; Dim df As PivotField
gt; gt; Dim pi As PivotItem
gt; gt; Dim pd As Range
gt; gt; Dim str As String
gt; gt; Set pt = Sheets(quot;Summary Pivotquot;).PivotTables(1)
gt; gt; Set df = pt.PivotFields(quot;2005 Volquot;) 'data field
gt; gt; Set pf = pt.PivotFields(quot;Customerquot;) 'column field
gt; gt; rTop = 7 'number of rows before data starts
gt; gt; For Each pi In pf.PivotItems
gt; gt; On Error Resume Next
gt; gt; pi.Visible = True
gt; gt; Next pi
gt; gt; i = pf.PivotItems.Count rTop
gt; gt; For r = i To rTop - 1 Step -1
gt; gt; On Error Resume Next
gt; gt; str = Cells(r, 1).Value
gt; gt; Set pd = pt.GetPivotData(df.Value, pf.Value, str)
gt; gt; If pd.Value = 0 Then
gt; gt; pf.PivotItems(str).Visible = False
gt; gt; End If
gt; gt; Next r
gt; gt; End sub
gt; gt;
gt; gt;
gt; gt; How do I modify this to recognize the 2 different levels of grouping? Thank
gt; gt; you!!
gt; gt;
gt; gt; --
gt; gt; maryj


An alternative may be to use 'sumif' and check for which values sum to
zero, set a label in a helper column to quot;do not displayquot; if zero else
quot;displayquot;. Put the label in the PAGE area and filter to select only the
'display' items.--
steven1001
------------------------------------------------------------------------
steven1001's Profile: www.excelforum.com/member.php...oamp;userid=30757
View this thread: www.excelforum.com/showthread...hreadid=528232