Results 1 to 2 of 2

Using VBA to collapse pivotfields when all labels of lower level pivotfields are "(blank)"

Threaded View

oceanracer Using VBA to collapse... 04-27-2021, 10:56 AM
oceanracer Re: Using VBA to collapse... 04-27-2021, 11:35 AM
  1. #1
    Registered User
    Join Date
    03-05-2013
    Location
    South Africa
    MS-Off Ver
    Excel 2019
    Posts
    2

    Using VBA to collapse pivotfields when all labels of lower level pivotfields are "(blank)"

    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.
    Last edited by oceanracer; 05-01-2021 at 08:32 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Pivot Table - Remove "Blank" count from Row Labels
    By EricaNiel in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 05-23-2020, 01:49 PM
  2. Replies: 0
    Last Post: 01-05-2017, 09:55 AM
  3. [SOLVED] If Statements for PivotFields?
    By EnigmaMatter in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-20-2014, 06:22 PM
  4. Trying to have the pivotfields(" ") be the cell content located in another worksheet
    By rtpuglisi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-10-2014, 04:06 PM
  5. Replies: 3
    Last Post: 02-16-2011, 02:55 PM
  6. Pivotfields & Pivotitems VBA
    By pickledmuffin in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-08-2008, 06:12 PM
  7. PivotFields problem
    By Tim in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-17-2005, 01:35 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1