ExcelBanter - Name a range

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Name a range (https://www.excelbanter.com/excel-programming/353881-name-range.html)

Pedro Costa[_3_]

Name a range

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[_6_]

Name a range

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" wrote in message
...
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

Name a range

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" wrote in message
...
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

Name a range

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

Name a range

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" wrote in message
...
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" wrote in message
...
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

Name a range

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

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

RefersToR1C1:=Selection.Address

Pedro Costa[_3_]

Name a range

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[_3_]

Name a range

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" wrote in message
...
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[_3_]

Name a range

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" wrote in message
...
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

All times are GMT +1. The time now is 04:16 PM.