Data Range for Graphs

[James]

I would like to "formularize" the data range for a graph
using VBA code so that when my data updates, I can update
my graph accordingly (i.e. when data updates, range for
graph changes).

Cannot figure out how to do this as Excel doesn't accept
the conventional VBA code for ranges when dealing with
graphs, at least it seems. When using Macro recorder, the
following is generated.

ActiveChart.SeriesCollection(1).XValues = "Summary!
R4C1:R39C1"

This doesn't work. I don't want to set the graph for a
very large range that I know will cover everything because
Excel will graph the null or zero values, greatly
distorting the graph.

Can anyone help.

Thanks!

 

[Andy Pope]

Hi James,

You can use Named ranges to dynamically update charts.

Check out Jon Peltier's examples.
http://peltiertech.com/Excel/Charts/Dynamics.html#DynColCht

Or Tushar Mehta's Dynamic Charts example
http://www.tushar-mehta.com/

Cheers
Andy