How to return a range address from a range name reference

  • Thread starter Thread starter David
  • Start date Start date
D

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
 
David said:
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)?
Click to expand...

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

=CELL("Address",MyRange)&":"&CELL("Address",
OFFSET(MyRange,ROWS(MyRange)-1,COLUMNS(MyRange)-1))
 
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))


.
Click to expand...
 
how about:

Range("MyRange").Address

Seems compact enough....... ...
Click to expand...

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.
 
Back
Top