+ Reply to Thread
Results 1 to 7 of 7

Convert 2 column range into multiple named ranges

  1. #1
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    2010
    Posts
    2,408

    Convert 2 column range into multiple named ranges

    I had some code to do this in a previous job but can no longer find it, despite an extensive google!

    If in a spreadsheet I selected a 2 column range and in the first column was the name I wished to create as the named range and the second column a formula that would be its values, the selection would create multiple named ranges in one go via VBA. rather than creating them 1 at a time


    A1= axisx B1=Offset($d$1,0,0,$c$1,1)
    A2= axisy1 B2=Offset($e$1,0,$d$2,$d$1,1)
    A3= axisy2 B3=Offset($d$1,0,Sd$3,$e$1,1)


    so selecting A1:b3 and running the vba code would generate 3 named ranges
    axisx which refers to Offset($d$1,0,0,$c$1,1)
    axisy1 which refers to Offset($d$1,0,0,$dc$1,1)
    axisy2 which refers to Offset($d$1,0,0,$e$1,1)

    however the code would work for any 2 column selection

    thanks for any assistance that can be given
    Last edited by davsth; 11-19-2020 at 05:28 AM.

  2. #2
    Banned User!
    Join Date
    02-06-2020
    Location
    Iowa City, IA, USA
    MS-Off Ver
    2016 - 365 / 2007
    Posts
    2,014

    Re: Convert 2 column range into multiple named ranges

    so what are you wanting to do again? name THREE 3 ranges in the form of entire columns with one code routine?

  3. #3
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    5,462

    Re: Convert 2 column range into multiple named ranges

    I've never heard of code that does that without looping. The closest thing I can think of is something like:

    Please Login or Register  to view this content.
    but the names would refer to the cells in the second column, not to the formulas they contain.
    Rory
    I drink, and I know things

  4. #4
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    2010
    Posts
    2,408

    Re: Convert 2 column range into multiple named ranges

    yes it was with looping in the solution I had previously. thank you for your help so far. Would you know the code to use it looping?

  5. #5
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    2010
    Posts
    2,408

    Re: Convert 2 column range into multiple named ranges

    If I have a 2 column by a variable number of rows selection, running the code will create a number of named ranges (in total the number of rows in the selection. In practice I could be naming many more than 3 ranges at the same time


    In the names manager the name will be taken from the left column and the value of the range the right

    in the case of A1= axisx B1=Offset($d$1,0,0,$c$1,1) the name of the range would be x axis and it would refer to =Offset($d$1,0,0,$c$1,1) , not cell B1. The solutuion Rory has kindly given creates the named range but refers to cell B1, which is not quite what I was looking for

    My end product would be something like this in name manager if that makes it any clearerAttachment 704841

    Its a bit frustating as I had the bit of code (which I even found by googling, but it was a few years ago), but my role has now moved back closer to what i was doing 8 years ago and I did not take it with me! Any help would be appreciated

  6. #6
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    5,462

    Re: Convert 2 column range into multiple named ranges

    The gist would be something like:

    Please Login or Register  to view this content.

  7. #7
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    2010
    Posts
    2,408

    Re: Convert 2 column range into multiple named ranges

    The above code from Rory worked perfectly as the body of a vba macro. It saves me hours. thank you for your assistance and Rory and your interest Adam

+ 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] Create multiple named ranges from a 2 column list
    By davsth in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-30-2017, 07:29 AM
  2. Replies: 3
    Last Post: 01-14-2015, 05:22 AM
  3. [SOLVED] Create macro that searches multiple named ranges and returns name of the range with data
    By mechant in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-15-2013, 01:04 AM
  4. Convert matrix into ranges to be used as named ranges
    By jeffreybrown in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-13-2010, 02:44 PM
  5. Multiple Ranges named as one range
    By cmcconna in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-27-2009, 09:41 AM
  6. [SOLVED] Ranges, adding a column to a named print range then saving the file
    By ross_smith@csaa.com in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-26-2006, 08:45 PM
  7. [SOLVED] Range matching multiple named Ranges
    By ben simpson in forum Excel General
    Replies: 0
    Last Post: 03-15-2006, 02:50 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