Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
maryj
 
Posts: n/a
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.misc
Nikki
 
Posts: n/a
Default 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

  #3   Report Post  
Posted to microsoft.public.excel.misc
maryj
 
Posts: n/a
Default 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

  #4   Report Post  
Posted to microsoft.public.excel.misc
steven1001
 
Posts: n/a
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Hide 0% figures in Pivot Table Matt D Francis Excel Worksheet Functions 4 November 4th 05 10:46 AM
Count unique values - Pivot Table Thomas Mueller Excel Discussion (Misc queries) 3 November 4th 05 12:55 AM
pivot table sort entries that don't yet appear in table rachael Excel Discussion (Misc queries) 11 September 19th 05 11:29 PM
Do not show rows with no values in Pivot Table Mark Excel Discussion (Misc queries) 0 April 19th 05 06:33 PM
Hide part of Pivot Table Ben Excel Worksheet Functions 1 March 8th 05 08:40 PM


All times are GMT +1. The time now is 05:54 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"