Using vba to filter pivot table top "xx"

[Russ15]

Currently I have a pivot table set up to show the top 1 of dates in the table. My question is, is there a way to filter the table to show the top 2 of dates if one of the value fields in the pivot table is empty using vba.

For example if the pivot table usually shows the top 1 of dates, if the pivot field "Fruit" is empty show the top 2 of dates instead.

I am using the pivot table to make calculations and if the sheet shows the top 1 of dates and the pivot field "Fruit" is empty, I am getting an error because it contains no value. In order to fix this I need to have it take the value in "Fruit" that is contained in the top 2 of dates instead.

Thanks in advance.

 

[Russ15]

bump

 

[Russ15]

Another approach I thought of/found online is hiding whatever value is empty in the "Fruit" field. I would need to "uncheck" whatever the latest date is in the pivot table and have the code check again for a value in the "Fruit" field. This loop would continue until finding a value in the "Fruit" field.

I think it would function something along the lines of:

1. set visible all items in the "Date" field
2. if the "Fruit" field has a value do the calculation
3. elseif the "Fruit" field is empty, uncheck the last item in the "Date" field
4. repeat the loop beginning at step 2.
5. after the calculation is completed, set all items back to visible in the "Date" field

 

[Russ15]

bump