REPLACE RANGE("A1:E20") with variable

[Guest]

I did a macro record building a chart and got this piece
of code...
..
..
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn,
TypeName:="Line - Column"
ActiveChart.SetSourceData Source:=Sheets
("prodcpu").Range("A1:E20"), PlotBy _
:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet
..
..
I can't use this as a permanent macro because the E20
might be E57(different number of entries) next week. The
A1 will always be the start cell.. NEED CODING TO REPLACE
A1:E20 as some variable Thanks.

 

[Jon Peltier]

There are a number of possibilities:

Sheets("prodcpu").Range("A1").CurrentRegion

Sheets("prodcpu").Range(Sheets("prodcpu").Range("A1"), _
Sheets("prodcpu").Range("E1").End(xlDown))

You might want to use a range variable:

Dim myRange as Range
Set myRange = [some kind of expression like above]
ActiveChart.SetSourceData Source:=myRange, _
PlotBy:=xlColumns

- Jon

 

[Guest]

PERFECT!! In the words of IBM, "OK TO CLOSE INCIDENT".
Thank you.

-----Original Message-----
There are a number of possibilities:

Sheets("prodcpu").Range("A1").CurrentRegion

Sheets("prodcpu").Range(Sheets("prodcpu").Range("A1"), _
Sheets("prodcpu").Range("E1").End(xlDown))

You might want to use a range variable:

Dim myRange as Range
Set myRange = [some kind of expression like above]
ActiveChart.SetSourceData Source:=myRange, _
PlotBy:=xlColumns

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

I did a macro record building a chart and got this piece
of code...
..
..
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn,
TypeName:="Line - Column"
ActiveChart.SetSourceData Source:=Sheets
("prodcpu").Range("A1:E20"), PlotBy _
:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet
..
..
I can't use this as a permanent macro because the E20
might be E57(different number of entries) next week. The
A1 will always be the start cell.. NEED CODING TO REPLACE
A1:E20 as some variable Thanks.

.