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?

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?

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?

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?

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?

VBA Code to name a variable range
 

or withOUT selections from anywhere in the workbook

Sub namerange()
x = Cells(Rows.Count, "a").End(xlUp).Row
Sheet1.Range("a1:a" & x).Name = "myname"
End Sub

--
Don Guillett
SalesAid Software

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

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?