VBA pivotTable.rowField question

[bmurn]

ActiveSheet.PivotTables("Rank Hospital Pivot").PivotFields("GroupInd").CurrentPage = Range("SelectGroupInd").Value

So I have drop down boxes on the front page of my excel sheet and want to filter pivot tables that the data is read from based on these drop down boxes.

All the drop downs which reference page fields in the pivot table are easily switched, it is the row fields which are giving me trouble.

Based on the drop down, only one rowfield value can be selected so I need to change the pivot table from showing all of the values in the row field at once to showing just one of the values in the row field. I was thinking I could find a way in VBA to hide all values in the row field and then show the one (out of about 10k) that I want to show (hiding 9,999 doesn't sound ilke the right approach).

Is there an easy way to do this or another way to do this? I want to avoid hiding rows manually in the VBA and would rather directly address the pivot field, just to keep my extraneous formulas on the same sheet stable.

Thanks!

 

[Jerry Sullivan]

This thread shows how to use two or more cells with DataValidation dropdowns to set pivotfilters.

http://www.mrexcel.com/forum/showthread.php?t=575835

Regarding the best way to set a row field with 10,000 items to just show one, in VBA I don't think there is any way to code: "hide all pivotitems for this rowfield then unhide this one pivotitem". Excel wants you to have at least one item set to visible at all times in VBA.

The two best approaches that I know of are:
1. Set your one pivotitem to visible= true, then step through each pivot item, testing each items visible state and only hiding it if it is currently visible. (This is the approach in the linked example).

2. Temporarily change the Orientation of the Pivotfield to xlPageField. When it's a PageField you can clear all the filters and just set the one you want. Then reset the Orientation to RowField and reset the Position.

The second option has the potential to run faster for a large number of pivotitems; however more things can go wrong if you don't reset everything back the same way as it was before changing orientation.