Suppressing Zero Rows in Pivot Tables

[mrp]

I am creating a simple Pivot Table that will be contantly
refreshed on a bi-weekly basis. The pivot table has
employee name in the row, nothing in the column, and a sum
field as data.

I want to know if there is a way that when the sum is
zero, that record will be suppressed, basically isolating
only those employees who have errors (a number in the
variance field.

The reason I am using a pivot table is there are multiple
offsetting transactions for each employee.

Thanks

 

[Debra Dalgleish]

You could use code similar to the following, to hide items with zero
total for a calculated field (Excel 2002):

'========================================
Sub HideZeroCalcItemRows()
'hide rows that contain zeros for calculated items
'by Debra Dalgleish
Dim r As Range
Dim pt As PivotTable
Dim pf1 As PivotField
Dim pf2 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 pf1 = pt.PivotFields("Year") 'column field
Set pf2 = pt.PivotFields("Rep") 'row field
Set pi = pf1.PivotItems("YearVar") 'calculated item

For Each r In pt.DataBodyRange.Rows
On Error Resume Next
str = Cells(r.Row, 1).Value
Set pd = pt.GetPivotData(df.Value, pf1.Value, pi.Value, pf2.Value, str)
If pd.Value = 0 Then
r.EntireRow.Hidden = True
Else
'unhide any previously hidden rows
r.EntireRow.Hidden = False
End If
Next r
End Sub
'==================================

 

[nc]

Debra

Does this code only work for Excel 2002? I am running
Excel 2000.

Thanks