VBA - Get data range of chart

[spectrallypure]

Dear all,

I have several charts in a sheet for which I need to change the data
ranges. Currently each chart has a different data range, and for each
chart the new desired data range is just an straightforward extension
of the original (i.e., the new data ranges could be easily calculated
from the original ones just by adding a fixed number of rows/
columns).

I am therefore trying to implement a macro for updating the data
ranges of my charts automatically, but so far I haven't found a way
for getting the actual data range of a chart. I guess I'm looking for
something like the opposite of ActiveChart.SetSourceData, i.e.
something like "ActiveChart.GetSourceData".... does anybody know of a
way of getting the current data range of a chart?

Thanks in advance for any help / ideas!

Regards,

Jorge Luis.

 

[Don Guillett]

Lots of different ways to do this. Look here
http://peltiertech.com/Excel/Charts/index.html

 

[Shane Devenshire]

Hi,

I didn't go out to Jon's site and check around but here is one simple way
without VBA assuming you are using Excel 2003 or later.

1. Select the data in the spreadsheet and press Ctrl+L, OK. This will
convert the data range into a list.

The chart will automatically add new data as it is entered below that chart.
You can convert the range to a list before or after you create the chart.
Your new data must be directly below the previous data. This technique only
works in the vertical direction. You should probably use dynamic range
names for horizontal data.

Cheers,
Shane