Adding a Name range with vba
 

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

Adding a Name range with vba
 

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." wrote in message
...
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

Adding a Name range with vba
 

On 3 Feb, 00:12, "Jan T." wrote:
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