ExcelBanter - How do I return the values of a chart's data range?

In the trivial case, in which all series share the same X values and all
ranges (the X and all the Ys) are parallel, starting and ending at the
same row (if by columns, the same column if by rows), you can run
through John's class module, and use Union(Range1, Range2, ...) to get
the chart's data range. As soon as one series has a different X range,
or a different number of points, you can't even get the source data
range manually from the Source Data dialog. Instead, you are told "The
Chart Data Range is too complex to be displayed. If a new Data Range is
selected, it will replace all of the series on the Series Panel."

If you are defining the source data range yourself, why not define a
named range that refers to this range, and use this defined range in
your set source data command:

ChartObjects(1).Chart.SetSourceData _
Source:=Range("DataRange"), _
PlotBy:=xlColumns

where DataRange is the name of a dynamic range in the worksheet.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Tom Ogilvy wrote:
See John Walkenbach's site:

http://j-walk.com/ss/excel/tips/tip83.htm
A Class Module to Manipulate a Chart SERIES
Excel's object model has a serious flaw: There is no direct way to to use
VBA to determine the ranges used in a chart. This tip contains a useful
class module that can simplify the task of manipulating chart's using VBA.

--
Regards,
Tom Ogilvy

Dave F wrote in message
...

Hi

Excel '2000 SR-1

I have a Line type chart embedded in a worksheet.
It has a single line of information (called a series I believe).

I know how to set the series data range (SetSourceData) , but I'm having a
lot of trouble getting the range.

Please point me in the correct direction.

Thanks in advance

Dave F.