Finding/Changing Range from existing Excel Chart

[campster]

I have an Excel template that contains numerous pre-defined charts and a program that opens this template and collects data used to populate the charts. This works great but I now want to adjust the series length so that the charts only used the data collected (not a range of 32000). I'm trying to iterate through the charts and determine the range for each series of each chart but not having much luck. The snippet below allows me to see the xvalues but does not return any information as to where this data is located.

Does anyone know how to extract the range for each series in a chart?

For Each oCht As Excel.Chart In oExcel.Workbooks(1).Charts
 
  Dim cnt As Integer = CType(oCht.SeriesCollection, Excel.SeriesCollection).Count
  For i As Integer = 1 To cnt
    oSeries = oCht.SeriesCollection(i)
    x = oSeries.XValues
 
    oRng = CType(CType(oCht.SeriesCollection, Excel.SeriesCollection).Item(i), Excel.Range)
  Next
Next

BTW, I know how to set the range using

oCht.SetSourceData(oShtData.Range(strRng), Excel.XlRowCol.xlColumns)

The problem is that I need to know the current range so that I can simply modify the max length of the series.

[campster]

Here's the solution that I came up with. Is there a better way?

Public Sub setSeriesLength()
 
        Dim oShtData As Excel.Worksheet
        Dim oSeries As Excel.Series
        Dim rng As String
        Dim tRng As String
        Dim tempRng As String
        Dim strAlpha() As String
 
        oShtData = CType(oExcel.Workbooks(1).Worksheets("data"), Excel.Worksheet)
        Dim rows As Integer = oShtData.UsedRange.Rows.Count
        tRng = "A2:A" & rows
        rng = ""
 
        For Each oCht As Excel.Chart In oExcel.Workbooks(1).Charts
            Dim cnt As Integer = CType(oCht.SeriesCollection, Excel.SeriesCollection).Count
            rng = ""
            For i As Integer = 1 To cnt
                oSeries = oCht.SeriesCollection(i)
                tempRng = CType(oCht.SeriesCollection, Excel.SeriesCollection).Item(i).Formula
                strAlpha = tempRng.Trim.Split("$"c)
                If strAlpha(0).ToLower.Contains("data") Then
                    tempRng = strAlpha(UBound(strAlpha) - 1) & "2:" & strAlpha(UBound(strAlpha) - 1) & rows
                    If (Len(rng) > 0) Then
                        rng = rng & "," & tempRng
                    Else
                        rng = tempRng
                    End If
 
                    oSeries.Values = oShtData.Range(rng)
                    oSeries.XValues = oShtData.Range(tRng)
                End If
                
            Next
            oCht.SetSourceData(oShtData.Range(tRng & "," & rng), Excel.XlRowCol.xlColumns)
 
        Next
    End Sub