Name a range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Pedro Costa - 21 Feb 2006 09:55 GMT

I'm trying to select a range and name it but i just cant do it
The range isn't always the same, it can start in R5C1 and ends at R27C76, or
R7C1 to R150C12.

Range("A1").Select
Selection.End(xlDown).Select

' this is where the data range begins

Selection.End(xlDown).Select
FirstRow = Selection.Row

Selection.End(xlDown).Select
LastRow = Selection.Row

Range("A1").Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select

FirstColumn = Selection.Column
Selection.End(xlToRight).Select

LastColumn = Selection.Column

After getting the range i would like to name it, like this

ActiveWorkbook.Names.Add Name:="DataRange",
RefersToR1C1:="=Sheet4!R5C1:R35C10"

How can i set the ..., "RefersToR1C1:="=Sheet4!R5C1:R35C10"" to my DataRange
selected above?

I'm sure theres a very easy way to do this but i'm new in VBA

Thanks
PC

Reply to this Message

Bob Phillips - 21 Feb 2006 10:23 GMT

Range("A1").Select
Selection.End(xlDown).Select

' this is where the data range begins

Selection.End(xlDown).Select
firstrow = Selection.Row

Selection.End(xlDown).Select
lastrow = Selection.Row

Range("A1").Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select

FirstColumn = Selection.Column
Selection.End(xlToRight).Select

lastcolumn = Selection.Column

Range("A" & firstrow, Cells(lastrow, lastcolumn)).Name = "DataRange"

Signature

HTH

Bob Phillips

(remove nothere from email address if mailing direct)

> I'm trying to select a range and name it but i just cant do it
> The range isn't always the same, it can start in R5C1 and ends at R27C76, or
[quoted text clipped - 32 lines]
> Thanks
> PC

Reply to this Message

Pedro Costa - 21 Feb 2006 10:50 GMT

thanks, it also worked very well

Thanks

> Range("A1").Select
> Selection.End(xlDown).Select
[quoted text clipped - 56 lines]
> > Thanks
> > PC

Reply to this Message

Norman Jones - 21 Feb 2006 10:24 GMT

Hi Pedro,

Try, perhaps:

'=============>>
Public Sub Tester021()
Dim rng As Range

Set rng = Range("A1").End(xlDown).End(xlDown).CurrentRegion
rng.Name = "DataRange"

End Sub
'<<=============

---
Regards,
Norman

> I'm trying to select a range and name it but i just cant do it
> The range isn't always the same, it can start in R5C1 and ends at R27C76,
[quoted text clipped - 34 lines]
> Thanks
> PC

Reply to this Message

Norman Jones - 21 Feb 2006 10:34 GMT

Hi Pedro,

Alternatively, if the range should be contained in a single row, try:

'=============>>
Public Sub Tester021A()
Dim rng As Range

Set rng = Range("A1").End(xlDown).End(xlDown). _
CurrentRegion.Resize(1)

rng.Name = "DataRange3"

End Sub
'<<=============

---
Regards,
Norman

> Hi Pedro,
>
[quoted text clipped - 52 lines]
>> Thanks
>> PC

Reply to this Message

Pedro Costa - 21 Feb 2006 10:46 GMT

thanks, it worked just fine, and in a very easy way.

Thanks
PC

> Hi Pedro,
>
[quoted text clipped - 52 lines]
> > Thanks
> > PC

Reply to this Message

Gary''s Student - 21 Feb 2006 10:31 GMT

You are almost there. You have Selected your range. Try

RefersToR1C1:=Selection.Address

Signature

Gary's Student

> I'm trying to select a range and name it but i just cant do it
> The range isn't always the same, it can start in R5C1 and ends at R27C76, or
[quoted text clipped - 32 lines]
> Thanks
> PC

Reply to this Message

keepITcool - 21 Feb 2006 10:43 GMT

GS..

Selection.address => returns an absolute A1 address.
Doesn't make sense to assign that to the R1C1 argument..

Selection.Name = "MyRange"

is the preferred syntax, as it will also work on (complex) multiarea
ranges, where the Address() string > 255 chars

Signature

keepITcool

| www.XLsupport.com | keepITcool chello nl | amsterdam

> You are almost there. You have Selected your range. Try
>
> RefersToR1C1:=Selection.Address

Reply to this Message

Pedro Costa - 21 Feb 2006 10:46 GMT

After selected the range i've tried this:
ActiveWorkbook.Names.Add Name:="DataRange", RefersToR1C1:=Selection.Adress

and got the error: "Object doesnt support this property or method"

what am i doing wrong?

> You are almost there. You have Selected your range. Try
>
[quoted text clipped - 36 lines]
> > Thanks
> > PC

Reply to this Message