+ Reply to Thread
Results 1 to 13 of 13

Deselect Item in Pivot Filter - VisibleItemsList

  1. #1
    Forum Contributor
    Join Date
    06-28-2018
    Location
    New York
    MS-Off Ver
    MS Office 2010
    Posts
    175

    Deselect Item in Pivot Filter - VisibleItemsList

    Hi All,

    The below code allows me to select only zero items in my pivot table. Is there a way to modify this to de-select zeros (I basically want to remove zero amounts from my pivot). Thank you in advance for any assistance!

    Please Login or Register  to view this content.

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,720

    Re: Deselect Item in Pivot Filter - VisibleItemsList

    .VisibleItemsList can only set list of visible items as it's name suggests.

    You will need to construct array of pivot items that you want to show.

    You can loop over the PivotFiels().PivotItems to construct your array.

    Though you may find it's far easier to control Pivots via manual slicer selection rather than code (or through use of DAX measure if that's available).

    Ex:
    Please Login or Register  to view this content.
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  3. #3
    Forum Contributor
    Join Date
    06-28-2018
    Location
    New York
    MS-Off Ver
    MS Office 2010
    Posts
    175

    Re: Deselect Item in Pivot Filter - VisibleItemsList

    Thanks CK76. I tried your code and received a run-time error 13 (type mismatch) on the last line of code.

    I've been researching this trying to find a way to use HiddenItemsList with no success. I really just want to ensure that each month as my data changes I remove all zeros from the GL Balance.

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,720

    Re: Deselect Item in Pivot Filter - VisibleItemsList

    Upload sample workbook. If you need specific help.

    Type mismatch indicates to me it is likely that there is no item added to iDic.

    Did you have pivot table filtered and showing only .&[0] item showing when the code was run?

    Try changing code to...
    Please Login or Register  to view this content.
    pvF.ClearAllFilters will ensure that filter is reset before the code runs.

  5. #5
    Forum Contributor
    Join Date
    06-28-2018
    Location
    New York
    MS-Off Ver
    MS Office 2010
    Posts
    175

    Re: Deselect Item in Pivot Filter - VisibleItemsList

    Thanks again CK76. No filters were applied previously, and the updated code returns the same error on the same line. I can try to modify the file and upload a sample as the information is sensitive. I appreciate your help.

  6. #6
    Forum Contributor
    Join Date
    06-28-2018
    Location
    New York
    MS-Off Ver
    MS Office 2010
    Posts
    175

    Re: Deselect Item in Pivot Filter - VisibleItemsList

    I tried stepping through the code and noticed that it skips over the If statement completely. It goes from the For Each to the last line of code then errors out.

  7. #7
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,720

    Re: Deselect Item in Pivot Filter - VisibleItemsList

    Wait... that means pvI (Pivot Item) has no item that passes If condition...

    I'll need to look at your pivot's OLAP model to really see what's going on.

    You can desensitize data, for your model. Make it static model with connection/query killed and upload it here. I can take a deeper look at what's going on.

    Tested code on one of my OLAP pivots. Had no issue.

  8. #8
    Forum Contributor
    Join Date
    06-28-2018
    Location
    New York
    MS-Off Ver
    MS Office 2010
    Posts
    175

    Re: Deselect Item in Pivot Filter - VisibleItemsList

    Here is the revised file. Thanks again!
    Attached Files Attached Files

  9. #9
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,720

    Re: Deselect Item in Pivot Filter - VisibleItemsList

    Ah, you have it in page field. That's the issue.

    PageField cannot be looped using the method, and it does not have VisibleItemsList property.

    Code would work as is, if you add the field to "Row Label" field.

    Alternative is to create Slicer using the field. And control it via Slicer.

    Are you open to using Slicer for this field?

  10. #10
    Forum Contributor
    Join Date
    06-28-2018
    Location
    New York
    MS-Off Ver
    MS Office 2010
    Posts
    175

    Re: Deselect Item in Pivot Filter - VisibleItemsList

    I am open to everything!

  11. #11
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,720

    Re: Deselect Item in Pivot Filter - VisibleItemsList

    With slicer there's no need to clear filter. So code would be something like below.
    SlicerCacheLevels is needed to work with OLAP based slicer cache (to deal with hierarchy columns/slicer).
    But this one only have single level, you can use SlicerCacheLevels(1) without issue.

    Please Login or Register  to view this content.
    See sample with Slicer added. Code is in Module5.
    Attached Files Attached Files

  12. #12
    Forum Contributor
    Join Date
    06-28-2018
    Location
    New York
    MS-Off Ver
    MS Office 2010
    Posts
    175

    Re: Deselect Item in Pivot Filter - VisibleItemsList

    Awesome CK76. Thank you for all your time and assistance!

  13. #13
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,720

    Re: Deselect Item in Pivot Filter - VisibleItemsList

    You are welcome and thanks for the rep

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Filter pivot item by date - type mismatch
    By mushkitoes in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-21-2018, 02:26 PM
  2. Pivot Table Filter Not Allowing Item Selection.
    By Greg Reed in forum Excel General
    Replies: 0
    Last Post: 07-29-2015, 09:37 AM
  3. Default pivot filter to the first item on opening workbook
    By kev_33 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-01-2015, 04:00 AM
  4. Hide a Pivot filter item
    By FixandFoxi in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 08-29-2013, 07:09 AM
  5. Finding OLAP Pivot VisibleItemsList values
    By 100_Meridian in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-01-2012, 02:48 PM
  6. Pivot Table Will Not Filter A Single Character Item
    By SndGenRX7 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-26-2012, 09:05 AM
  7. How do I deselect an item from a ListBox
    By skysurfer in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-27-2010, 10:59 AM

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