getting the absolute range address from a dynamic named range

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.

Enter this tiny UDF:


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

then =addr(PTA) should get you the address
--
Gary's Student


"junoon" wrote:

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

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



--
Regards,
Tom Ogilvy

"junoon" wrote:

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