I have 4 pivot table filters and I have set up the rest of a dashboard using the cube formulas. This is all working great. Finally, when the user changes a pivot field filter, the CUBERANKEDMEMBERS are ordered on a tuple such that sometimes the values are blank.
I have a macro to hide/show these blank rows - but what I want to do is call the hide/show macro when a filter changes and the new values has been retrieved from the cube. I have tried using various combination of Worksheet_Change and Worksheet_Calculate but the results are proving to be reliable. The result will then be almost like a pivot table but much more flexible. Any help is much appreciates.
Thanks an advance for your time - I am using excel 2010 for this BTW.