Creating 100s of Range Names using VBA

[bnoc7312]

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

 

[MickG]

Try this for data in row 1

[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]

Thank you for the swift reply! I implemented the code, however it only assigns the first cell (A1) the reference name....

 

[Norie]

Ian

What ranges/items will these named ranges refer to?

 

[MickG]

The code is designed for row(1) not column (1), Which is it you want ?????