ExcelBanter - Hide zero column in Pivotreport

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

'==========================
Sub HideZeroColumnTotals()
'hide columns that contain zero totals
'by Debra Dalgleish
Dim r As Integer
Dim i As Integer
Dim rTop 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
For Each pi In pf.PivotItems
On Error Resume Next
pi.Visible = True
Next pi
rTop = 4 'heading row
i = pf.PivotItems.Count
For r = i To 1 Step -1
On Error Resume Next
str = Cells(rTop, r).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
'===================================

Tobias wrote:
Hello all,

I have a number of process sections that I keep track of. It is how much was produced in each section per week. When I want to create a chart from this it would be nice to hide the zero columns. (Containing the weeks when nothing was produced.) I have a script for hiding zero rows, but I'm not able to transform it to columns also.

Thanks in advance.

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html