How to get the series range of a chart ...

[krishnak]

Hi All,

I have a chart with one SeriesCollection. I want to get the range of cells for Y-Axis and X-Axis for this chart using VBA.
The Series object has 'Values' and 'XValues' properties that return the values in an array.
Is there any way that I can get the range of cells (cell addresses) for the Series?
Or is there a way to get the range address on the worksheet from the array values?
Thanks in advance

[CatDaddy]

[vba]Dim cA() As Variant
Dim i as integer
Dim cell as Range
i=0

For each cell in Range(somerange)
cA(i) = cell.Address
i = i + 1
Next cell

Worksheets(1).ChartObjects(1).Chart. _
SeriesCollection.Extend cA[/vba]

untested

[p45cal]

According to John Walkenbach: "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."
You can explore parsing the series formula, or use his class module.
See:
http://spreadsheetpage.com/index.php..._chart_series/
http://www.j-walk.com/ss/excel/tips/tip83.htm

[krishnak]

Thanks for the replies.
For a simple chart, the Formula for the Series can be parsed to get the range, but for a chart that uses filtered values from a large dataset, the process gets really complicated.
For the present, we have to accept the limitation and do the best we can.