Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot table question: How to display total and percent for data simultaneouly | Excel Discussion (Misc queries) | |||
pivot table | Excel Discussion (Misc queries) | |||
Pivot Table Question | Excel Discussion (Misc queries) | |||
Jon Peltier - Pivot Table Result for yesterday's "Complex Chart" Question | Charts and Charting in Excel | |||
pivot table question, sum fields? | Excel Worksheet Functions |