getting the absolute range address from a dynamic named range

[junoon]

Hi,

i have created a dynamic NAMED range for columns of data on Sheet1.

PTA=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))

I am trying to get the range address of the Named Range, into a single
cell say, A655536...

A655536=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))

i can get the First address of the range say, $A$1, using
Cell("Address", PTA), but cannot get the Last address of it.

Also, is there a way to acheive this programmatically, so that i can
then dump the address into a variable, say....External Data variable
called .Name....

..Name="Sheet1!" & Cell("contents", B65536)

PLEASE HELP ASAP.

 

[Gary''s Student]

Enter this tiny UDF:


Function addr(r As Range) As String
addr = r.Address
End Function

then =addr(PTA) should get you the address

 

[Tom Ogilvy]

Using a cell as an intermediary seems like the long way around the block.

set rng = Range("PTA")
msgbox rng(1).Address & " - " & rng(rng.count).Address