Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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),CO UNTA(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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
getting the absolute range address from a dynamic named range
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),CO UNTA(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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
getting the absolute range address from a dynamic named range
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),CO UNTA(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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Named Range | Excel Discussion (Misc queries) | |||
Dynamic Named Range | Charts and Charting in Excel | |||
dynamic named range | Excel Programming | |||
Address of named range | Excel Worksheet Functions | |||
Named Range Cells vs. Absolute Cell Addresses | Excel Programming |