PDA

View Full Version : [SOLVED] How to get the series range of a chart ...



krishnak
08-17-2012, 08:31 AM
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
08-17-2012, 09:05 AM
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

untested

p45cal
08-20-2012, 02:11 AM
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/tip/a_class_module_to_manipulate_a_chart_series/
http://www.j-walk.com/ss/excel/tips/tip83.htm

krishnak
08-21-2012, 08:53 AM
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.