Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Defining a range using defined names
Hi I have a range that that starts at
$A$2 which is named "dataStart" and ends with $A$10 which is named "DataEnd". I want to create a named range using the defined names, e.g dataList refers to =dataStart:DataEnd except that the above syntax does not work. can someone tell me the correct syntax for this, as I don't want to use =$A$2:$A$10. The values for dataStart and dataEnd may change which is why I do not want to hard code them. Thanks, Chris |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Defining a range using defined names
Chris,
try this =OFFSET(INDIRECT(ADDRESS(ROW(DataStart), COLUMN(DataStart))),0,0,ROW(DataEnd)-ROW(DataStart)+1,COLUMN(DataEnd)-COLUMN(DataStart)+1) -- Hope that helps. Vergel Adriano "Chris Wilkinson" wrote: Hi I have a range that that starts at $A$2 which is named "dataStart" and ends with $A$10 which is named "DataEnd". I want to create a named range using the defined names, e.g dataList refers to =dataStart:DataEnd except that the above syntax does not work. can someone tell me the correct syntax for this, as I don't want to use =$A$2:$A$10. The values for dataStart and dataEnd may change which is why I do not want to hard code them. Thanks, Chris |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Defining a range using defined names
Sub chris()
Set rs = Range("datastart") Set re = Range("dataEnd") Set rt = Range(rs, re) MsgBox (rt.Address) rt.Name = "datalist" End Sub -- Gary''s Student - gsnu200722 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Defining a range using defined names
Set myRange = Range(Range("DataStart"),Range("DataEnd"))
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Chris Wilkinson" wrote in message ... Hi I have a range that that starts at $A$2 which is named "dataStart" and ends with $A$10 which is named "DataEnd". I want to create a named range using the defined names, e.g dataList refers to =dataStart:DataEnd except that the above syntax does not work. can someone tell me the correct syntax for this, as I don't want to use =$A$2:$A$10. The values for dataStart and dataEnd may change which is why I do not want to hard code them. Thanks, Chris |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Defining a range using defined names
Or
=INDIRECT(ADDRESS(ROW(datastart),COLUMN(datastart) ) & ":" & ADDRESS(ROW(dataend),COLUMN(dataend))) -- p45cal "Vergel Adriano" wrote: Chris, try this =OFFSET(INDIRECT(ADDRESS(ROW(DataStart), COLUMN(DataStart))),0,0,ROW(DataEnd)-ROW(DataStart)+1,COLUMN(DataEnd)-COLUMN(DataStart)+1) -- Hope that helps. Vergel Adriano "Chris Wilkinson" wrote: Hi I have a range that that starts at $A$2 which is named "dataStart" and ends with $A$10 which is named "DataEnd". I want to create a named range using the defined names, e.g dataList refers to =dataStart:DataEnd except that the above syntax does not work. can someone tell me the correct syntax for this, as I don't want to use =$A$2:$A$10. The values for dataStart and dataEnd may change which is why I do not want to hard code them. Thanks, Chris |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Defined Range Names in formulas | Excel Discussion (Misc queries) | |||
Help defining a user-defined function | New Users to Excel | |||
Rename Several Defined Range Names with Macro | Excel Programming | |||
Listing or Changing all Defined Range Names (using VBA) | Excel Programming | |||
Defining worksheet specific range names | Excel Programming |