Name a range

Pedro Costa · 02-21-2006, 06:00 AM

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

Bob Phillips · 02-21-2006, 06:25 AM

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"

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Pedro Costa" <[email protected]> wrote in message
news:[email protected]...
> 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

Norman Jones · 02-21-2006, 06:30 AM

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

"Pedro Costa" <[email protected]> wrote in message
news:[email protected]...
> 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

Gary''s Student · 02-21-2006, 06:35 AM

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

RefersToR1C1:=Selection.Address
--
Gary's Student

"Pedro Costa" wrote:

> 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

Norman Jones · 02-21-2006, 06:40 AM

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

"Norman Jones" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
>
> "Pedro Costa" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>
>

keepITcool · 02-21-2006, 06:50 AM

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

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

Gary''s Student wrote in
<news:<[email protected]>

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

Pedro Costa · 02-21-2006, 06:50 AM

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?

"Gary''s Student" wrote:

> You are almost there. You have Selected your range. Try
>
> RefersToR1C1:=Selection.Address
> --
> Gary's Student
>
>
> "Pedro Costa" wrote:
>
> > 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

Pedro Costa · 02-21-2006, 06:50 AM

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

Thanks
PC

"Norman Jones" wrote:

> 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
>
>
>
> "Pedro Costa" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

Pedro Costa · 02-21-2006, 06:55 AM

thanks, it also worked very well

Thanks

"Bob Phillips" wrote:

> 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"
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Pedro Costa" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>