Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Stevek
 
Posts: n/a
Default Pivot Table question


Is there a way to automatically hide data with results = 0 on a pivot
table? I have a long list of output, but the majority of data points
are = 0. I only want to show output with positivr results, thereby
limiting the size of the table.


--
Stevek
------------------------------------------------------------------------
Stevek's Profile: http://www.excelforum.com/member.php...o&userid=32134
View this thread: http://www.excelforum.com/showthread...hreadid=518884

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max
 
Posts: n/a
Default Pivot Table question

"Stevek" wrote:
Is there a way to automatically hide data with results = 0 on a pivot
table? I have a long list of output, but the majority of data points
are = 0. I only want to show output with positivr results, thereby
limiting the size of the table.


Try the extract below, taken from Debra Dalgleish's response
to a previous similar query at:
http://tinyurl.com/5xlen

-------- quote -----
You could use a macro to hide the rows. For example:

'=============================
Sub HidePivotZeroRows()
'hide worksheet rows that contain all zeros
Dim rng As Range
For Each rng In ActiveSheet _
.PivotTables(1).DataBodyRange.Rows
If Application.Sum(rng) = 0 Then
rng.EntireRow.Hidden = True
Else
'unhide any previously hidden rows
rng.EntireRow.Hidden = False
End If
Next rng
End Sub
'=================================

Or for Excel 2002 or later:
'==========================
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("Pivot").PivotTables(1)
Set df = pt.PivotFields("Units") 'data field
Set pf = pt.PivotFields("Rep") 'column field
rTop = 4 '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
'===================================

suhair wrote:
Hi,
Can i get help!!
I have a pivot table, and in the data i have a sum of
quantity of items, i dont want to see items that there
sum is zero, (the zero is because of number of records
that there sum is zero!!!).
Can u help me to hide these items?
Thank you,
Suhair


--- unquote ---

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
--


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
steven1001
 
Posts: n/a
Default Pivot Table question


another possible approach..
Assuming the data is in the spreadsheet, in the column next to the
value column just use a formula like =if(<cell=0,"hide","display").
Include the column in the pivot data range and then just put this
column label in the PAGE area and select only "display".

regards..


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

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
Pivot table question: How to display total and percent for data simultaneouly [email protected] Excel Discussion (Misc queries) 1 January 18th 06 08:12 PM
pivot table rmsterling Excel Discussion (Misc queries) 5 November 14th 05 05:40 PM
Pivot Table Question chance2motor Excel Discussion (Misc queries) 1 July 6th 05 09:10 PM
Jon Peltier - Pivot Table Result for yesterday's "Complex Chart" Question Barb Reinhardt Charts and Charting in Excel 3 December 8th 04 02:48 AM
pivot table question, sum fields? Todd L. Excel Worksheet Functions 2 November 30th 04 06:07 AM


All times are GMT +1. The time now is 04:40 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"