Suppress pivot table lines

[Guest]

I'm creating a budget pivot table. Its' source is a worksheet in the same
workbook that is linked to individual department budget workbooks. A general
budget template was used for all departments and many of the accounts have a
zero value in the amount column. The pivot table picks up all the accounts,
including the ones with zero values. Is there any way to suppress these lines
without actually deleting the linked rows? I'd like to keep the pivot table's
workbook flexible and generic but it prints an excessive amount of rows with
no numeric values.

Thanks,

TomD

 

[Debra Dalgleish]

The following macro will hide the rows with zero total, in Excel 2002
or later, where the row field is "Rep", and data field is "Units"

'==========================
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
'===================================