REPLACE RANGE("A1:E20") with variable

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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
 
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/
_______

[email protected] said:
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.
Click to expand...

.
Click to expand...
 
Back
Top