Filtering a Pivot Table

[Guest]

I have a pivot table where I sum the long distance phone charges based on
accounting code numbers of about 150 phones. Sometimes, there will be no
charges to an account number. I would like to filter out the codes that have
a zero dollar amount, so only the account codes with charges will show
(eliminating about 2 pages of unnecessary codes). I have tried to use an
autofilter, but then it will hide the information in the sheet, as the pivot
table is placed in the sheet is linked to. Placing the pivot table on its
own sheet is not an option, as this workbook is already too large.

 

[Frank Kabel]

Hi
i think placing the pivot table on its own sheet is really the best
option

 

[Debra Dalgleish]

The following macro will hide the row items with zero total, in Excel
2002 or later, where the row field is "Code", and data field is "Amount"

'==========================
Sub HideZeroRowTotals()
'hide rows that contain zero totals
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("Amount") 'data field
Set pf = pt.PivotFields("Code") 'row 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
'===================================