Pivottable Uncheck All

J

Jo

Hi,

I have a pivottable with a high number of data entries.
This makes for very difficult reading and sometimes i
would like to have single entries for analytical purposes.
Does anyone know how to uncheck all entries in a
pivotfield so that i ca check the one entry that i want?

Cheers,
Jo
 
J

jeff

Hi,

If I'm not mistaken you should be able to click on the
down arrow in the drop boxes and uncheck "Show All" -then
select those entries you want.

jeff
 
B

Bernie Deitrick

Jo,

The message below is originally from Debra Dalgleish.

HTH,
Bernie
MS Excel MVP

You can do this with a macro. The first macro hides all items except the
last one, and the second one shows all items. Replace "Rep" with the
name of your field.

Sub PivotHideItemsField()
'For version 2000 -- hide all items in specific field
'sort is set to Manual to prevent errors, e.g.
'unable to set Visible Property of PivotItem class
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Set pt = ActiveSheet.PivotTables(1)
Set pf = pt.PivotFields("Rep")
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
With pt.PivotFields("Rep")
pf.AutoSort xlManual, pf.SourceName
For Each pi In pf.PivotItems
pi.Visible = False
Next pi
pf.AutoSort xlAscending, pf.SourceName
End With
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Sub PivotShowItemsField()
'For version 2000 -- show all items in specific field
'sort is set to Manual to prevent errors, e.g.
'unable to set Visible Property of PivotItem class
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Set pt = ActiveSheet.PivotTables(1)
Set pf = pt.PivotFields("Rep")
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
With pt.PivotFields("Rep")
pf.AutoSort xlManual, pf.SourceName
For Each pi In pf.PivotItems
If pi.Visible <> True Then
pi.Visible = True
End If
Next pi
pf.AutoSort xlAscending, pf.SourceName
End With
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
 
Top