I am working with a pivot table with 4 hierarchical (or nested) levels (i.e. dimensions) or "Rows" in Pivotable layout's jargon when setting up PivotTable Fields interactively. Some data have been recorded only at level 1 (highly aggregated) so that subsequent levels are blank (i.e. showing "(blank)" labels) while others have been recorded at the highest level of disaggregation or level 4. So I end up with a pivot table that looks like that (with .ShowDetail set to True for all levels that is when expanding all PivotFields).. I am not showing any data value here as it is irrelevant in this case but there are 1 more column with value in it (not shown here) :
-level1 label 1
....-level2 label 1.1
........- level3 label 1.1.1
............- level4 label 1.1.1.1
............- level4 label 1.1.1.2
-level1 label 2
....-(blank)
........-(blank)
............-(blank)
-level1 label 3
....-level2 label 3.1
........-(blank)
............-(blank)
I'd like to run some VBA codes to collapse particular levels (ShowDetail = False except of course for the lowest level which cannot be collapsed) when all corresponding sublevels are blanks (i.e. "(blank)"). for example after running the VBA codes, my 3rd entry would then look like that:
-level1 label 3
....+level2 label 3.1
without the (blank) labels showing
my 2nd entry would then look like that after running the same VBA codes:
+level1 label 2
This is to avoid showing any "(blank)" labels at any level in not only the pivot table but especially in the corresponding pivot chart.
I tried to add conditional codes with .Visible = False but that makes the entire data for that thread disappeared if it find at least a (blank) at any of the 4 levels. I can use conditional .ShowDetail=False for a given level if its value is "(blank)", it then will show +(blank) but that does not apply to the level above it which should collapse as well to hide the +(blank) label.
I must add that if any level has a "(blank)" label then all levels below will also have a "(blank)" label.
Any help would be appreciated
cheers.
Bookmarks