Defining a range using defined names

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chris Wilkinson - 17 May 2007 12:53 GMT

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 to this Message

Vergel Adriano - 17 May 2007 13:19 GMT

Chris,

try this

=OFFSET(INDIRECT(ADDRESS(ROW(DataStart),
COLUMN(DataStart))),0,0,ROW(DataEnd)-ROW(DataStart)+1,COLUMN(DataEnd)-COLUMN(DataStart)+1)

Signature

Hope that helps.

Vergel Adriano

> Hi I have a range that that starts at
> $A$2 which is named "dataStart" and ends with
[quoted text clipped - 8 lines]
> Thanks,
> Chris

Reply to this Message

p45cal - 17 May 2007 14:21 GMT

Or
=INDIRECT(ADDRESS(ROW(datastart),COLUMN(datastart)) & ":" &
ADDRESS(ROW(dataend),COLUMN(dataend)))

Signature

p45cal

> Chris,
>
[quoted text clipped - 15 lines]
> > Thanks,
> > Chris

Reply to this Message

Gary''s Student - 17 May 2007 13:32 GMT

Sub chris()
Set rs = Range("datastart")
Set re = Range("dataEnd")
Set rt = Range(rs, re)
MsgBox (rt.Address)
rt.Name = "datalist"
End Sub

Signature

Gary''s Student - gsnu200722

Reply to this Message

Bob Phillips - 17 May 2007 13:52 GMT

Set myRange = Range(Range("DataStart"),Range("DataEnd"))

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Hi I have a range that that starts at
> $A$2 which is named "dataStart" and ends with
[quoted text clipped - 10 lines]
> Thanks,
> Chris

Reply to this Message