Archive of Mr Excel Message Board

Steve
I had a similar problem.
Say my source data was in A1:B6.
In C1 I put a formula like this "A1:"&"B"&Counta(B:B) to give me the expanding range address of my source data.
I then tried to get the chart to accept this formula as the source area =Indirect(C1).
It didn't seem to like this so I wrote a macro telling it to do virtually the same thing i.e.

ActiveChart.SetSourceData Source:=Sheets("sheetname").Range(Range("sheetname!C1").Value), PlotBy:= etc

It seems perfectly happy with this so I now have a macro button labelled "Update Chart" which works brilliantly.
I obtained the code by using the recorder.
Hope this helps you
Derek

Hello, *trying* to creat a Chart on a varying size range. For example I imported these 3 columns of numbers starting at A1 1/22/2002 3.732051 4.383049 1/23/2002 3.732051 4.09845 a Chart and customized chart type and all the labels, etc. I saved this as a template, Chart.xlt with my customized chart format, (Chart.xl) but it will only graph the 3 data series above, i.e, only range A1.C3 1/22/2002 3.732051 4.383049 1/23/2002 3.732051 4.09845 1/24/2002 3.146264 4.157029 1/25/2002 4.236068 4.230234 1/26/2002 4.236068 4.230234 it still only graphs A1.C3, ignoring any data beyond the original range when I created the template file. What do I have to do to get Excel to graph the CurrentRegion ? - steve

Thanks Derek - the thing about that solution -
and it DOES work - is that along with a new
source of data, any Series configurations I had in the
template become reset to defaults (i.e., I lose them)

So the solution for me was to create the template with
source data having the largest number of rows I could
think of (for ex: 500) and customize the chart based upon
some 500 rows of dummy data. Then I delete the range and
set current cell to A1 and save the XLT template.

As long as any new WKS I open within this template has LESS
info (i.e., fewer than 500 rows) then the chart seems to like
it and scales the x and y axis according to the data. And I
get to keep my Series and legend customizations.

- steve

Steve I had a similar problem. Say my source data was in A1:B6. In C1 I put a formula like this "A1:"&"B"&Counta(B:B) to give me the expanding range address of my source data. I then tried to get the chart to accept this formula as the source area =Indirect(C1). It didn't seem to like this so I wrote a macro telling it to do virtually the same thing i.e. I obtained the code by using the recorder. Hope this helps you Derek