Pivot Table - Zero Sum of Values

[nc]

How to avoid Zero Sum of Values appearing in a pivot table?

The field items does have data but the amounts cancel each
because they are equal and opposite.

 

[Debra Dalgleish]

The following code will hide row items with a zero total (Excel 2002):
'===============================
Sub HideZeroItemTotals()
'hide rows that contain zero totals
'by Debra Dalgleish
Dim r 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") 'row field

For Each pi In pf.PivotItems
pi.Visible = True
Next pi

i = pf.PivotItems.Count
For r = i To 1 Step -1
On Error Resume Next
str = Cells(r + 5, 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
'====================================

 

[nc]

Thank you for your response?

Will this code work in EXCEL 2000?

What I want is not only to hide the zero values but not to
be included in the report. This is important because I
will be using this table in a vlooup function.