| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I enter formula sum(range+range)*0.15 sumif(range=3) | Excel Discussion (Misc queries) | |||
| Excel Addin:Setting the range to the Excel.Range object range prop | Excel Worksheet Functions | |||
| Range Question / error 1004: method Range of object Worksheet has failed | Excel Programming | |||
| Range.Find returns cell outside of range when range set to single cell | Excel Programming | |||
| how to? set my range= my UDF argument (range vs. value in range) [advanced?] | Excel Programming | |||
![Pedro Costa[_3_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
