Pivot Table "Show All" Check Box missing in Excel 2000

[Guest]

Hope someone can help with this.

A large array of data I have is sorted and filtered using
a pivot table. Works well in XL2002 & XP, being able to
uncheck the show "ALL" box in the Pivot Table drop down
menu and then choosing only the category(ies) I want to
view.

However, when loaded on an Excel 2000 machine, the show
ALL box is not listed, only the individual choices.
Obivously to uncheck 100 options to view only 1 is not
going to work. Anyone have an explanation?

Appreciate any help in advance.

Curtis

 

[Debra Dalgleish]

You can use a macro to hide or show the items -- there's some sample
code on my web site:

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

 

[Curtis]

Thank you. That works really well. I'm assuming w/ a
little more code I could add a drop down to the "Show"
or "Hide" one item code that would list what items I have
available top choose from (they will always stay the
same)? If you happen to know that code too, I'd
appreciate it, otherwise I can figure it out.

Thanks again!

Curtis

 

[OwenLiu]

Dear All,

I am facing the same problem and hope soneone can provide the method t
display the "Show All" checkbox.

Thanks and best regards,
Owen Liu :confused

 

[Alan Davis]

Are you using an OLAP cube as your data source? You won't have that selection
available to you if you are. Here is some code that will do it.

Public Sub showAll()
On Error GoTo err_CmdShow:


' You can set the display setting for empty columns and empty rows that
'will be displayed

Dim pvtTable As PivotTable
Set pvtTable = ActiveSheet.PivotTables(1)
' Empty rows will be displayed
pvtTable.DisplayEmptyRow = True
'Empty columns will be displayed.
pvtTable.DisplayEmptyColumn = True

err_GetOut:
Exit Sub

err_CmdShow:
MsgBox Err.Description
GoTo err_GetOut
End Sub

 

[Debra Dalgleish]

If you're using Excel 2000, or an earlier version, the Show All checkbox
isn't available. You can use a macro to hide or show the items --
there's some sample code on my web site:

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