Excel VBA : Chart : How do you determine, programmatically the chart range

Despite searching Google, I don't seem to be able to find the answer to my question/problem.

So, say a chart has a range something like:

Formula:

Please Login or Register  to view this content.

how would I return that range address?

Rider: there are several charts on several sheets so, how would I loop through the sheets and, within each sheet, return the chart title and range address for each chart.

I know that I can set the range using

Please Login or Register  to view this content.

but I need to be able to establish what it currently is in order that I can adjust it.

Do I need to select the sheet and/or the chart (making it active) in order to test and set the range?

Thanks in advance

TMS

Try this:

Please Login or Register  to view this content.

It loops through each chart on each worksheet - for each chart, it loops through each series, and extracts the range from the series FORMULA property. We then union the series ranges to provide the overall chart range.

Perfick

Bit of tweaking as a) not all sheets have charts, and b) not all charts have split ranges.

But it does the trick

Thank you.

TMS

Originally Posted by TMS

b) not all charts have split ranges.

That doesn't actually make any difference - the series.formula property consists of 4 elements:
1. Series Name
2. XValues
3. Values
4. Plot Order

These elements are comma separated, so the SPLIT function just identifies the range of XValues and Values.

You may run into issues if the chart source is a direct data array, rather than a range... but that would also be easily handled.

Glad it pointed you in the right direction - thanks for the feedback

That doesn't actually make any difference

It seemed to; I needed to add on On Error Resume Next to cater for an error. Seemed OK after that.