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.
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.