How to return a range address from a range name reference

[David]

Greetings,
Range $A$1:$B$10 has been named "MyRange".
Is there a compact way of returning "$A$1:$B$10" by
refering to MyRange in a worksheet function(s)?
TIA
David

 

[Harlan Grove]

Range $A$1:$B$10 has been named "MyRange".
Is there a compact way of returning "$A$1:$B$10" by
refering to MyRange in a worksheet function(s)?

Yes, but it's arguable whether it's compact.

=CELL("Address",MyRange)&":"&CELL("Address",
OFFSET(MyRange,ROWS(MyRange)-1,COLUMNS(MyRange)-1))

 

[David]

Harlan,
Thanks very much indeed )
David

-----Original Message-----


Yes, but it's arguable whether it's compact.

=CELL("Address",MyRange)&":"&CELL("Address",
OFFSET(MyRange,ROWS(MyRange)-1,COLUMNS(MyRange)-1))


.

 

[Harlan Grove]

how about:

Range("MyRange").Address

Seems compact enough....... ...

Lemme reinforce the OP's specs: IN A WORKSHEET FUNCTION...

So only one small flaw in your suggestion: it requires VBA, and is therefore not
a function or formula solution. Yes, you could wrap it in a UDF, but it's still
a VBA solution.