VBA for getting Range of Name(Dynamic Range)

[ljtong]

Hi all,

I'm having trouble in getting the range that a name(dynamic range) refers to. I want the range so I can then go Range.select

why does this not work?

MsgBox (Workbooks("Gap").Worksheets("SlowDown").Names("Data").RefersToRange)

If I use

MsgBox (Workbooks("Gap").Worksheets("SlowDown").Names("Data").RefersTo)

I get the formula (=offset......) only

"Data" is actually "SlowDown!Data"


Also, what is the difference between select and activate?
as in worksheets("aa").select doesn't seem to be different from .activate

 

[just_jon]

MsgBox (Workbooks("Gap").Worksheets("SlowDown").Names("Data").Address

Is that it?

 

[ljtong]

Of course!! RefersToRange returns a Range...

MsgBox (Workbooks("Gap").Worksheets("SlowDown").Names("Data").RefersToRange.address)

 

[ljtong]

Ah.. we posted at the same time jon

names("data").address doesn't work unfortunately

RefersToRange.Address works ok now

any thoughts on the select/activate difference..?

 

[Joe Was]

Try this, you need to set a range object, but not the Sheet, only the Workbook needed!

Sub myRange()

'Set myWBS = Workbooks("Gap")
Set myWBS = ThisWorkbook

MsgBox myWBS.Names("Data")
End Sub