Name a range

[Guest]

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]

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)

 

[Norman Jones]

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
'<<=============

 

[Guest]

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

RefersToR1C1:=Selection.Address

 

[Norman Jones]

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
'<<=============

 

[keepITcool]

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

 

[Guest]

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?

 

[Guest]

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

Thanks
PC

 

[Guest]

thanks, it also worked very well

Thanks

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)