Dynamic Name Range using VBA

[Raj]

Hi,

I need to create sheet level Dynamic range names in a workbook using
VBA.. This is what I insert in the RefersTo box while creating a name
manually:

=OFFSET(Sheet1!$B$3,0,0,COUNTA(Sheet1!$B$3:$B$500),1)

How do I do it in VBA?

Thanks in Advance for the help.

Regards,
Raj

 

[Don Guillett]

Try recording a macro while doing
Sub Macro3()
'
' Macro3 Macro
' Macro recorded 4/20/2010 by Donald B. Guillett
'

'
ActiveWorkbook.Names.Add Name:="xxx", RefersToR1C1:= _
"=OFFSET(Sheet1!R1C1,1,1)"
End Sub
============
or
Sub makename()
ActiveWorkbook.Names.Add Name:="yyy", RefersTo:= _
"=OFFSET(Sheet1!$B$3,0,0,COUNTA(Sheet1!$B$3:$B$500),1)"
End Sub