Dynamic Name Range using VBA


R

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
 
Ad

Advertisements

D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top