Try this:
Sub foo()
Dim ws As Worksheet
Dim chto As ChartObject
Dim srs As Series
Dim sRng() As String
Dim rng As Range
Dim i As Integer
For Each ws In ThisWorkbook.Worksheets
For Each chto In ws.ChartObjects
For Each srs In chto.Chart.SeriesCollection
sRng = Split(srs.Formula, ",")
For i = 1 To UBound(sRng) - 1
If rng Is Nothing Then
Set rng = Range(sRng(i))
Else
Set rng = Union(rng, Range(sRng(i)))
End If
Next i
Next srs
Debug.Print chto.Chart.Name, chto.Chart.ChartTitle.Text, rng.Address(external:=True)
Set rng = Nothing
Next chto
Next ws
End Sub
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.
Bookmarks