Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code to name a variable range
Hi,
I have a macro to create charts. Problem is that the source data is variable by lenght....could be between 10-15 rows. Columns are always constant. I can't use the code ActiveWorkbook.Names.Add Name:="RangeName", RefersTo:=Selection because you have to specify the cell refs. Any ideas please? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code to name a variable range
hi,
assumeing that your source data in a A1 down...... the key would be to select the variable range first. try something like this.... Sub macSetRName() Sheets("YourSheet").Select Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveWorkbook.Names.Add Name:="TheRangeName", RefersTo:=Selection End Sub Post back if this don't work for you. FSt1 "John" wrote: Hi, I have a macro to create charts. Problem is that the source data is variable by lenght....could be between 10-15 rows. Columns are always constant. I can't use the code ActiveWorkbook.Names.Add Name:="RangeName", RefersTo:=Selection because you have to specify the cell refs. Any ideas please? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code to name a variable range
You could use similar to this...
ActiveWorkbook.Names.Add Name:="RangeName", _ RefersTo:=Sheet1.Range("A1", Sheet1.Range("D65536").End(xlUp)) -- HTH... Jim Thomlinson "John" wrote: Hi, I have a macro to create charts. Problem is that the source data is variable by lenght....could be between 10-15 rows. Columns are always constant. I can't use the code ActiveWorkbook.Names.Add Name:="RangeName", RefersTo:=Selection because you have to specify the cell refs. Any ideas please? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code to name a variable range
Alternative
Worksheets("Sheet1").Range("D1").Resize(Cells(Rows .Count,"D").End(xlUp).Row) .. _ Name = "RangeName" -- HTH RP (remove nothere from the email address if mailing direct) "Jim Thomlinson" wrote in message ... You could use similar to this... ActiveWorkbook.Names.Add Name:="RangeName", _ RefersTo:=Sheet1.Range("A1", Sheet1.Range("D65536").End(xlUp)) -- HTH... Jim Thomlinson "John" wrote: Hi, I have a macro to create charts. Problem is that the source data is variable by lenght....could be between 10-15 rows. Columns are always constant. I can't use the code ActiveWorkbook.Names.Add Name:="RangeName", RefersTo:=Selection because you have to specify the cell refs. Any ideas please? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code to name a variable range
This is spot on
Cheers John "FSt1" wrote: hi, assumeing that your source data in a A1 down...... the key would be to select the variable range first. try something like this.... Sub macSetRName() Sheets("YourSheet").Select Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveWorkbook.Names.Add Name:="TheRangeName", RefersTo:=Selection End Sub Post back if this don't work for you. FSt1 "John" wrote: Hi, I have a macro to create charts. Problem is that the source data is variable by lenght....could be between 10-15 rows. Columns are always constant. I can't use the code ActiveWorkbook.Names.Add Name:="RangeName", RefersTo:=Selection because you have to specify the cell refs. Any ideas please? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code to name a variable range
Selection.Name = "TheRangeName"
- Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ FSt1 wrote: hi, assumeing that your source data in a A1 down...... the key would be to select the variable range first. try something like this.... Sub macSetRName() Sheets("YourSheet").Select Range("A1").Select Range(Selection, Selection.End(xlDown)).Select ActiveWorkbook.Names.Add Name:="TheRangeName", RefersTo:=Selection End Sub Post back if this don't work for you. FSt1 "John" wrote: Hi, I have a macro to create charts. Problem is that the source data is variable by lenght....could be between 10-15 rows. Columns are always constant. I can't use the code ActiveWorkbook.Names.Add Name:="RangeName", RefersTo:=Selection because you have to specify the cell refs. Any ideas please? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
can i link a variable cost code with a variable sum | Excel Discussion (Misc queries) | |||
how do I hard code a variable range for a pivot table in vba? | Excel Programming | |||
variable code | Excel Programming | |||
setting a range variable equal to the value of a string variable | Excel Programming | |||
Problem trying to us a range variable as an array variable | Excel Programming |