How to de-select all items in Pivot tables

  • Thread starter Thread starter twaccess
  • Start date Start date
T

twaccess

I am currently working on very large databases in a pivot table.

When I want to focus on a particular item, I use the item drop down t
de-select manually all the items in the list. Then select the item(s)
want to view in the table.

Is there an easier way than this manual de-selecting ?

Your help would be much appreciated.

Terr
 
hi Terry

not sure how much faster it will be, but you can select the ones you don't
want to see and right mouse click on them and choose "hide" - this is the
same (AFAIK) as "unticking" them in the drop down list.

Cheers
JulieD
 
I wondered also if the following code could be made to work to help with
deselecting ?

' Keyboard Shortcut: Ctrl+d
'
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Prom
Date")
PivotItems("?").Visible = False
PivotItems("17/04/2003").Visible = False
PivotItems("28/05/2003").Visible = False
PivotItems("29/05/2003").Visible = False
PivotItems("02/06/2003").Visible = False
PivotItems("04/06/2003").Visible = False
PivotItems("05/06/2003").Visible = False
PivotItems("06/06/2003").Visible = False
PivotItems("09/06/2003").Visible = False
End With
End Sub
 
You need to hop on over to Debra Dalgleish's site, and she has a routine that
will do exactly that for you. It will deselect all items bar one, and leaves
you free to add back what you want.

http://www.contextures.com/xlPivot03.html

Scroll down till you see the Show All Items -- Excel 97/Excel 2000 and Hide
All Items -- Excel 97/Excel 2000
sections.

The first routine will add them all back for you as well.

This should get you sorted, but if you have any problems, or have never run
macros and are unsure of anything, then just post back.
 
Thanks Ken

I did try and create a macro using the code on this website, but
couldnt seem to get it to work.

If you dont mind providing a quick run-down I would be grateful.

Thanks

Terr
 
Where did you paste the code, and how are you running the macro?
What doesn't work? Does the macro run, but not clear all the items?
 
I'm afraid this may sound a bit of a crude way of doing this, but I
created a macro with Ctrl+Shift+L to run it.

Deleted the nonsense stuff and inserted your code in its place and then
ran it... ??

Here's what I did...

Sub Macro7()
'
' Macro7 Macro
' Macro recorded 09/11/2003 by Terryw
'
' Keyboard Shortcut: Ctrl+Shift+L
'
'Sub PivotShowItemAllVisible()
'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
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
For Each pt In ActiveSheet.PivotTables
For Each pf In pt.VisibleFields
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
Next pf
Next pt
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Thanks for your help.

Terry
 
Back
Top