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

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

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

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

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