PDA

View Full Version : Solved: How to Hide Pivot rows with empty DataBodyRangevalues



Onetrack
03-19-2013, 03:59 AM
I am trying to hide pivot rows with empty values in the databody range.

The following code (source http://www.contextures.com/xlPivot03.html processes the rowfield values. How can this be modified to hide the rows if the corresponding databody value is empty?

Also, why does this code require the following:

"pf.AutoSort xlManual, pf.SourceName". I'd rather retain the default sortorder of the pivot table.

Sub HidePivotItemsVisible()
'pivot table tutorial by contextures.com
'hide all pivot items in all tables on sheet
'except last item
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
For Each pt In ActiveSheet.PivotTables
For Each pf In pt.RowFields
pf.AutoSort xlManual, pf.SourceName
For Each pi In pf.PivotItems
pi.Visible = False
Next
Next
pf.AutoSort xlAscending, pf.SourceName
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Thank you very much.

Onetrack
03-19-2013, 09:15 AM
OK - I have found the solution, here:http://www.ozgrid.com/VBA/hide-pivot-fields.htm