+ Reply to Thread
Results 1 to 7 of 7

Combine all Named Ranges into one Named Range

  1. #1
    Registered User
    Join Date
    08-23-2012
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2010
    Posts
    57

    Question Combine all Named Ranges into one Named Range

    Hey Guys,

    I have 11 Named Ranges (all near or at capacity) in a workbook and I am writing some code that will do different things with those Named Ranges. Instead of something like the following:

    Please Login or Register  to view this content.
    and having to list each range and repeat code, is there a way to combine all of my named ranges into a range/string?

    I have tried this one below, but it does not work :
    Please Login or Register  to view this content.
    I couldn't get any of this to work either:
    Please Login or Register  to view this content.
    My ultimate goal is to use - Range("XXX").Select, where "XXX" references all named ranges I define in my workbook. Hopefully someone can tell me what I am missing or a better way to call them.
    A macro does what you tell it, not what you want.

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,658

    Re: Combine all Named Ranges into one Named Range

    Please Login or Register  to view this content.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    08-23-2012
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2010
    Posts
    57

    Re: Combine all Named Ranges into one Named Range

    Thanks for the reply shg, and Howdy from East Texas!

    I am not sure exactly how that code would work. Any time I try to include multiple Named Ranges into one range(), the code fails.

    Here is an example-

    This Based on a dropdown selection in a cell, a macro is called and runs a Sub similar to:
    Please Login or Register  to view this content.
    However, if I attempt to add more than 1 Named Range, the code fails with Method 'Range' of object '_Global' failed.
    Last edited by thinkspac; 11-11-2012 at 05:12 PM. Reason: additional information added

  4. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,658

    Re: Combine all Named Ranges into one Named Range

    Post a workbook that illustrates the problem.

  5. #5
    Registered User
    Join Date
    08-23-2012
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2010
    Posts
    57

    Angry Re: Combine all Named Ranges into one Named Range

    Figured out my error - I did not realize that the Named Ranges were case sensitive when called in VBA.

  6. #6
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,658

    Re: Combine all Named Ranges into one Named Range

    They're not ...

  7. #7
    Registered User
    Join Date
    02-23-2020
    Location
    Fort Worth, Texas, USA
    MS-Off Ver
    2016
    Posts
    1

    Re: Combine all Named Ranges into one Named Range

    I just encountered this 13 year old thread today when I went in search of a way to combine seven named ranges into one logical named range. I suppose I could manually combine them into one disjoint range, since the ranges are not adjacent to each other, but I hoped for something a tad simpler, so that I can use the aggregated range in four worksheet formulas to get the overall count, minimum, maximum, and average of the cells in the seven ranges.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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