+ Reply to Thread
Results 1 to 3 of 3

Selecting multiple ranges in PivotTable, then grouping

  1. #1
    Forum Contributor
    Join Date
    07-24-2008
    Location
    Athome
    MS-Off Ver
    Excel 2013
    Posts
    125

    Selecting multiple ranges in PivotTable, then grouping

    Hello good people.

    I've attached a sample file. There is some code in the sample file in Module 1.
    Main issue here is selecting multiple cells for grouping, but the number of cells changes each time.

    I have a reference sheet which contains "Long descriptions". These values are contained in the pivottable.
    In the adjacent column, I type in the "short description".

    The "short description" is NOT unique to each long description i.e. it applies to more than one line.
    This can differ each month
    eg. in current month, there could be 3 Long Descrip that fall under Short Descrip1.
    Next month, there could be 5 Long Descrip that fall under the Short Descrip1.

    The macro code in Module 1 goes to the pivottable, and brings back the row reference on which the LongDescrip appears.
    From this point onwards, I am failing.

    I need the macro to go back to the pivottable, select all items under ShortDescrip1, group these items, then rename the group to ShortDescrip1.
    It should do this for all the short descriptions.

    I've put in a table of what the desired result looks like.
    Pretty easy to understand when you see the sample file.

    I tried Union function but can't seem to get it to work because the number of items falling under a Short Descrip keeps changing each month, thus changing the number of expressions to include in the multiple selection.
    Attached Files Attached Files
    Last edited by rasonline; 01-13-2014 at 06:57 AM. Reason: solved

  2. #2
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Selecting multiple ranges in PivotTable, then grouping

    You may try this:
    Please Login or Register  to view this content.
    • Please remember to mark threads Solved with Thread Tools link at top of page.
    • Please use code tags when posting code: [code]Place your code here[/code]
    • Please read Forum Rules

  3. #3
    Forum Contributor
    Join Date
    07-24-2008
    Location
    Athome
    MS-Off Ver
    Excel 2013
    Posts
    125

    Re: Selecting multiple ranges in PivotTable, then grouping

    Thanks again Izandol! Works perfectly.

    A bit difficult to understand this solution though.
    Had a read here about Dictionaries so thanks for also teaching me about a new feature in VBA.

    Trying to get my head around the code applied to the pivot table now.

+ 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. [SOLVED] Cannot open PivotTable with Multiple Consolidation Ranges
    By bigN in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-08-2012, 09:34 AM
  2. Selecting multiple ranges
    By F6Hawk in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-22-2008, 02:22 AM
  3. PivotTable - Multiple Consolidation Ranges
    By Alan in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-10-2006, 10:00 AM
  4. Selecting multiple ranges
    By john@fairstead.net in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-02-2006, 02:50 PM
  5. [SOLVED] selecting multiple ranges
    By Helen in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-16-2005, 12:06 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