Adding a Name range with vba

[Jan T.]

Hi. I have successfully made Dynamic Name Ranges through the Excel
Insert/Name menu. In the Refers To text box I would write something
like:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

I tried vba code like:
Activeworksheet.Names.Add ...... and so on.

However, when I try to write code in vba to do this, it fails.
How should I write the code without the ReferTo part to fail?

Thanks a lot for any help!

Regards
Jan

 

[Ron Coderre]

Try this:

ActiveWorkbook.Names.Add _
Name:="MyDynRng", _
RefersTo:="=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)"

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Jan T.]

Hi. I have successfully made Dynamic Name Ranges through the Excel
Insert/Name menu. In the Refers To text box I would write something
like:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

I tried vba code like:
Activeworksheet.Names.Add ...... and so on.

However, when I try to write code in vba to do this, it fails.
How should I write the code without the ReferTo part to fail?

Thanks a lot for any help!

Regards
Jan

Thank you so much. That was excactly what I needed!

Regards
Jan