Creating 100s of Range Names using VBA

bnoc7312

New Member
Joined
Sep 15, 2014
Messages
4
Hello,

I am currently working on spread sheets that contain hundreds of items, each requiring a range name.

Is there a way of using VBA to automate creating a range name for each item?

I have a row with 100 items called fc_1, fc_2,... etc.

Thank you in advance for your help.

Ian
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try this for data in row 1
Code:
[COLOR="Navy"]Sub[/COLOR] MG15Sep34
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
 [COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Cells(1, Columns.Count).End(xlToLeft))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]With[/COLOR] Dn
        .Name = .Value
    [COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 

bnoc7312

New Member
Joined
Sep 15, 2014
Messages
4
Thank you for the swift reply! I implemented the code, however it only assigns the first cell (A1) the reference name....
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
Ian

What ranges/items will these named ranges refer to?
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
The code is designed for row(1) not column (1), Which is it you want ?????
 

Watch MrExcel Video

Forum statistics

Threads
1,108,923
Messages
5,525,651
Members
409,658
Latest member
Yardcell

This Week's Hot Topics

Top