Using the SeriesCollection.Extend method

Question

I would like to use SeriesCollection.Extend method to extend the range of a chart. Worksheet(1) has the values

Column A	Column B
DATE
6/1/2023	1
6/2/2023	4
6/3/2023
6/4/2023

The first 3 rows/columns are charted as "Chart 1". I want to extend the chart to include row 4 using the SeriesCollection.Extend method.

The macro:

Sub ExtendTrial()

Charts("Chart 1").SeriesCollection.Extend _
Source:=Worksheets("Sheet1").Range("$A$5:$B$5")

End Sub

gives a "Subscript out of range" error. The macro does not seem to allow me to add the xlColumn parameter

double check the spelling of `Chart 1` it probably is `Chart1` — Scott Craner, Jul 19 '23 at 17:54
Changing to "Chart1" does not help. There definitely is a Sheet1, trying Worksheets(1) does not work either. But thanks! :) — oldsilenus, Jul 19 '23 at 19:41

score 1 · Answer 1 · answered Jul 20 '23 at 04:20

I needed to use ChartObject in Excel 2019 to remove your error message and specify optional argument values to get the correct plot rather than let Excel try, and fail, to determine.

Option Explicit

Sub ExtendTrial()

    Worksheets("Sheet1").ChartObjects("Chart 1").Chart.SeriesCollection.Extend _
        Source:=Worksheets("Sheet1").Range("$A$5:$B$5"), Rowcol:=xlColumns, CategoryLabels:=True
End Sub

Using the SeriesCollection.Extend method

1 Answers1