Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide zero column in Pivotreport
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hide Column If Usedrange of Column ISBLANK Follow-up | Excel Discussion (Misc queries) | |||
Hide Column If Usedrange of Column ISBLANK | Excel Discussion (Misc queries) | |||
How to hide a column | Excel Discussion (Misc queries) | |||
Hide column or row based on a cell in the column or row? | Excel Discussion (Misc queries) | |||
hide column | New Users to Excel |