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

Answer 2

Your line of code

.SeriesCollection.NewSeries.XValues = Sht1.Range("F36", Sht1.Range("F36").End(xlToRight))

is adding a third series via .NewSeries. Change it to

.SeriesCollection(1).XValues = Sht1.Range("F36", Sht1.Range("F36").End(xlToRight))

Now there's nothing to delete.

Answer 3

THis doesn't seem to work always

.SeriesCollection(1).XValues = Sht1.Range("F36", Sht1.Range("F36").End(xlToRight))

This wont work if there is no series 3 to delete (some times randomly this series 3 doesn't appear by default then you'll get an error for this line )

MyChtObj.Chart.SeriesCollection(3).Delete

Here's a fast n dirty trick. Add this (repeated 3 times here but repeat more depending on your sheet because different sheets depending on the nr of cells populated, generate different numbers of random series). It will keep deleting the series that falls into the 3rd position in the "list" with every deletion until nr or series is 2

        If .SeriesCollection.Count > 2 Then
MyChtObj.Chart.SeriesCollection(3).Delete
        End If

        If .SeriesCollection.Count > 2 Then
MyChtObj.Chart.SeriesCollection(3).Delete
        End If

        If .SeriesCollection.Count > 2 Then
MyChtObj.Chart.SeriesCollection(3).Delete
        End If