Get Source Data Range for a Chart

[ttt123]

Hello,
I'm try to figure out how to find what the source data range a chart refers to is, but there isn't an obvious property or method to get this. Does anyone know how to do it? There is a Chart.SetSourceData but no Chart.GetSourceData.

Thanks,
Taylour

Sub test()
    Dim cht As Chart, rngSourceData As Range
    For Each cht In ActiveSheet.Charts
        'This method doesn't exist.
        'Set rngSourceData = cht.GetSourceData
        If Not (Intersect(rngSourceData, Range("A1:M8")) Is Nothing) Then
            cht.Delete
        End If
    Next cht
End Sub

 

[Jon Peltier]

This property is not exposed in VBA, probably for good reason. It doesn't take much to make this range too difficult to display.

You could, of course, loop through the series of a chart, parse the SERIES formulas of each series to get the ranges that contain name, X values, and Y values, then merge these ranges together. Note that Excel would not have showed a chart source data range in many cases:

1. Series don't all have the same X values.
2. Series don't all have the same number of points.
3. Series ranges do not fit into a rectangle (i.e., rows of data don't all stop and start in the same columns, or columns don't all stop and start in the same rows).
4. Series are not plotted in the order that their ranges appear in the worksheet.