Convert 2 column range into multiple named ranges

**davsth** · 11-18-2020, 08:59 AM

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

**vba_php** · 11-18-2020, 09:57 AM

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

**rorya** · 11-18-2020, 10:54 AM

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.

**davsth** · 11-19-2020, 04:14 AM

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?

**davsth** · 11-19-2020, 04:53 AM

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

**rorya** · 11-19-2020, 05:06 AM

The gist would be something like:

Please Login or Register  to view this content.

**davsth** · 11-19-2020, 07:01 AM

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

Convert 2 column range into multiple named ranges

LinkBack

Thread Tools

Rate This Thread

Display

Convert 2 column range into multiple named ranges

Re: Convert 2 column range into multiple named ranges

Re: Convert 2 column range into multiple named ranges

Re: Convert 2 column range into multiple named ranges

Re: Convert 2 column range into multiple named ranges

Re: Convert 2 column range into multiple named ranges

Re: Convert 2 column range into multiple named ranges

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Create multiple named ranges from a 2 column list

How to create multiple named ranges with a fixed size from one column?

[SOLVED] Create macro that searches multiple named ranges and returns name of the range with data

Convert matrix into ranges to be used as named ranges

Multiple Ranges named as one range

[SOLVED] Ranges, adding a column to a named print range then saving the file

[SOLVED] Range matching multiple named Ranges

Bookmarks

Bookmarks

Posting Permissions