Pivot Table: Selecting Drop Downs with Criteria

**VegasL** · 10-29-2009, 02:43 PM

Hello,

Using Excel 2003. Is there an easy way when using a pivot table, that when I select a column heading where it has the drop down arrow and "Show All" along with all the numbers assoicated to that column. If there are say 200 individual #s' in column, is there an easy way to select a range?

For example:

it has

Show All
# N/A
-5
-4
-3
-2
-1
0
1
2
3
4
etc all the way up to 200.

I want to select say only the negative numbers, or only numbers starting from 4 or Higher, how can I do this without having to manually click on each box for the range of criteria i want?

Thanks

**DonkeyOte** · 10-31-2009, 12:55 PM

Not that I am aware of... this is an interesting question to me at least.

I came up with the following...

First create a custom right click option to add to the PT right click menu

To be added to ThisWorkbook

Please Login or Register  to view this content.

To be added to a standard Module

Please Login or Register  to view this content.

Second insert the code to be run when the above right click option is invoked, again to be stored in a standard module:

Please Login or Register  to view this content.

What should now happen is that if you right click on your Pivot and opt to Apply Filter you should be presented with two consecutive dialogs for min & max parameters - the code should do the rest...
obviously to clear the filter use the standard functionality (ie All)

The above should work for both Row & Column fields.

I'm sure there's better code out there but this was what I came up with ...

On an aside if you do lots of PT work you might want to check out Debra Dalgleish's PT add-in

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

From what I could tell at first glance the above was not covered hence the grotesque code above.

(caveat is I've not tested in any great detail - ie where field may be grouped etc etc...)

EDIT:
I attached the small working file I used to test the above - as I say don't shoot me if it's not 100% watertight !
Code can be tidied up but it's more a case of proof of concept at this stage I think ?