Home » Category » Microsoft Excel

Microsoft Excel: zeros in pivot tables

205| Thu, 22 May 2008 22:39:00 GMT| anonymous| Comments (2)
Hi everybody,

I am working on an exell pivot table and I would like the pivot table not to
show the zeros on columns when the total for the line is actually zero. I
would like to supress this rows.

Do you have any input?

Thanks to all

Have a great day

Keywords & Tags: zeros, pivot, tables, microsoft, excel

URL: http://www.developertags.com/microsoft-excel/495042/
 
«« Prev - Next »» 2 helpful answers below.
Tomas,

You can set "Zero values" off in Tools - Options - View. It affects the
whole sheet.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
---

"Tomasmexicali...hotmail.com"
<Tomasmexicali...hotmail.com...discussions.microsoft.com> wrote in message
news:7BA8CE12-A688-4D24-A175-A52FD4C15985...microsoft.com...
> Hi everybody,
> I am working on an exell pivot table and I would like the pivot table not
to
> show the zeros on columns when the total for the line is actually zero. I
> would like to supress this rows.
> Do you have any input?
> Thanks to all
> Have a great day

earl | Thu, 22 May 2008 22:40:00 GMT |

The following code will hide the rows with zero total:

'==========================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
'===================================
Tomasmexicali...hotmail.com wrote:
> Hi everybody,
> I am working on an exell pivot table and I would like the pivot table not to
> show the zeros on columns when the total for the line is actually zero. I
> would like to supress this rows.
> Do you have any input?
> Thanks to all
> Have a great day
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

debra | Thu, 22 May 2008 22:41:00 GMT |

Microsoft Excel Hot Answers

Microsoft Excel New questions

Microsoft Excel Related Categories