Remove series from chart with VBA

Question

I am creating a chart in excel via vba code. I am using contiguous data and the chart pops up no problem, however there is an extra series named "Series 3" that I didn't ask for and need to get rid of (via deleting it or omitting in the first place). It has no data in it but needs to be removed from the legend at least. Here is my code :

Dim MyChtObj As ChartObject
Dim Sht1 As Worksheet
Dim ShtName As String

Set Sht1 = Worksheets("Parameter Forecasts")
ShtName = Sht1.Name
Set MyChtObj = Sht1.ChartObjects.Add(100, 100, 500, 500)

Set a = Sht1.Range("E37", Sht1.Range("E37").End(xlToRight))
Set b = Sht1.Range("E38", Sht1.Range("E38").End(xlToRight))
Set InputData = Union(a, b)

With MyChtObj.Chart
    .ChartType = xlLineMarkers
    .SetSourceData InputData
    .PlotBy = xlRows
    .SeriesCollection.NewSeries.XValues = Sht1.Range("F36", Sht1.Range("F36").End(xlToRight))
End With

I have already tried:

MyChtObj.SeriesCollection(3).Delete

But this does not work.

Thanks in advance, Max

Answer 1

The SeriesCollection is part of the ChartObject.Chart object, and not the ChartObject.

Therfore, replace your line of:

MyChtObj.SeriesCollection(3).Delete

With:

MyChtObj.Chart.SeriesCollection(3).Delete