asked on

Removing “ALL” option in a Pivot field

In my Pivot table report, I have placed one Pivot field with orientation “xlPageField”. (Eg: Sex, with values “M”,”F”). Normally excel provides 3 options to select in such conditions (Viz: “(All)”, “M”, “F”). I want it to display only “M” and “F”.

I am NOT looking for a workaround solution like, after selecting “All” validating it in a event and changing it.

I want it to display only “M” and “F”.

pauloaguia

You cannot. It's by design.

I've checked the PivotTable through VBA and the PageFields have all the normal PivotItems. Thereis no "(All)" PivotItem that you can delete or anything. Excel just adds it there. And there is also no other option to deactivate it.

Sorry it's not the answer you want but it's the only possible. (at least on XL2K)

Paulo

amaronjob

ASKER

I also noticed no "(All)" PivotItem under PivotFields object, can you assure there is no OTHER tweaking possible to avoid "ALL'

ASKER CERTIFIED SOLUTION

pauloaguia

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

amaronjob

ASKER

I got a better suggestion from Microsoft community....I think this is the best and simple solution for my requirement...

* Disable ItemSelection for that pivotfield
* add one button near pivotfield

code for toggle button

With ActiveSheet.PivotTables("PT1").PivotFields("Sex")
..EnableItemSelection = True
If .CurrentPage = "M" Then
..CurrentPage = "F"
Else
..CurrentPage = "M"
End If
..EnableItemSelection = False
End With

pauloaguia

Apart from the fact that this suggestion has a toggle button instead of a combo box it's basically what I was telling you about :) (if you had more than two values you'd have to go to a combobox).

Is this closed then?

If so and you don't think any of the comments here answers your problem then pleas, post a 0 point question in Community Support Topic Area with a link to this one, asking a moderator to come by, deal with the question and refund your points. Otherwise please, accept the comment you believe is closer to the answer (and please, read the guidelines for grading answers before you do so).

If there are still any questions about this then please post them here.

Paulo

amaronjob

ASKER

Hi pauloaguia, Thanks for your effort in looking into this problem.

eventhough your comment/solution did not completly solve my problem, it did gave feed to my current solution.

If you are intrested in knowing how it is not complete enough, I will explain.

Many Thanks

pauloaguia

Thank you.

Yes, I am. I allways like to have an explanation when I get a C. Around here C's usually means some very serious problems with the expert (check this: https://www.experts-exchange.com/jsp/cmtyQuestAnswer.jsp#3)

So, yes, I am interested in knowing why this grade...

Paulo