getting the absolute range address from a dynamic named range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

junoon - 21 Mar 2006 13:35 GMT

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.

Reply to this Message

Gary''s Student - 21 Mar 2006 13:50 GMT

Enter this tiny UDF:

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

then =addr(PTA) should get you the address

Signature

Gary's Student

> Hi,
>
[quoted text clipped - 17 lines]
>
> PLEASE HELP ASAP.

Reply to this Message

Tom Ogilvy - 21 Mar 2006 14:29 GMT

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

Signature

Regards,
Tom Ogilvy

> Hi,
>
[quoted text clipped - 17 lines]
>
> PLEASE HELP ASAP.

Reply to this Message