+ Reply to Thread
Results 1 to 6 of 6

VBA Selecting Items in Pivot table filter based on criteria

  1. #1
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Minneapolis, Minnesota
    MS-Off Ver
    Office 365
    Posts
    124

    VBA Selecting Items in Pivot table filter based on criteria

    Hi,

    I have the following code:

    Please Login or Register  to view this content.
    i need a line that selects all items starting with "RMA" or "DMR" from the filter Base inside the Pivot table.

    Thansk for the help.

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

    Re: VBA Selecting Items in Pivot table filter based on criteria

    For regular pivot table, there is no method to select multiple items at once. You'll need to loop through each item in a given field.

    As well, each loop iteration causes pivot table to refresh slowing down the process, to avoid this, it is recommended that you set .ManualUpdate to True at start of loop and to False at end of loop.

    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
    08-10-2016
    Location
    Minneapolis, Minnesota
    MS-Off Ver
    Office 365
    Posts
    124

    Re: VBA Selecting Items in Pivot table filter based on criteria

    Something went wrong during execution. Please see attached image.Microsoft_Visual_Basic.jpgMicrosoft_Visual_Basic2.jpg

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

    Re: VBA Selecting Items in Pivot table filter based on criteria

    Hmm, try commenting out that line. Since you are clearing all filter and setting all items to visible at start, you really don't need to set it to .Visible = True.

    If that doesn't resolve your issue, please upload sample workbook so I can test out what need to be changed.

  5. #5
    Forum Contributor
    Join Date
    08-10-2016
    Location
    Minneapolis, Minnesota
    MS-Off Ver
    Office 365
    Posts
    124

    Re: VBA Selecting Items in Pivot table filter based on criteria

    So that worked, however, it took a long time to go through the list and filter out all other items. Is there a way to make the loop do it faster?
    The total items on the list varies from 400 to 800 items.
    Last edited by YuriyBaron; 05-01-2018 at 03:17 PM.

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

    Re: VBA Selecting Items in Pivot table filter based on criteria

    Not with regular pivottable, without making significant change to how pivot table is constructed.
    Especially since, ManualUpdate = True is already used.

    One approach you can test is following.

    Add helper column to source data. That identifies each row as RMA, DMR or Other.
    This can be done via code or formula (performance will depend on your workbook structure).

    Then add another slicer from pivot table (after extending data range), using the helper column.
    This will act as toggle for filtering individual items and will significantly improve performance over VBA (no need for code to loop each item).

    If further help is needed, please upload sample workbook with about 50 or so lines of data, or just enough to demonstrate your issue/need.

+ 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. Replies: 3
    Last Post: 03-03-2017, 12:32 PM
  2. [SOLVED] Rank items in Pivot table from high to low and low to high based on the filter selected
    By jholiday78 in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 02-19-2017, 11:22 AM
  3. Replies: 1
    Last Post: 07-16-2015, 05:46 AM
  4. selecting pivot table items
    By verschelde.steven in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-11-2014, 03:24 PM
  5. Change Pivot table Filter Based on Cell Value *Multiple Filter items* Possible?
    By Flydd in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-27-2012, 06:57 AM
  6. Hide/show specific Pivot Table Items based on multiple criteria
    By wotadude in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-18-2009, 05:15 PM
  7. Pivot Table Selecting Items
    By AK in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-02-2005, 03:06 PM

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