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

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." <(E-Mail Removed)> wrote in message
news:ec1002da-d453-4dbb-9402-(E-Mail Removed)...
> 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
>
>
>

On 3 Feb, 00:12, "Jan T." <Jan.Thorsten...@hotmail.com> 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