VBA create xy scatter plot using multiple data series

Question

I am trying to create an xy scatter plot on its own worksheet using VBA. I have two sets of data I'd like to use. When I run the code shown below for the first time (i.e. when there is no "Power Chart" present), it plots the data correctly. However, when I rerun the code with a "Power Chart" present, I get 3 additional series showing up, two with blank data and one with only y values, corresponding to the last column in my worksheet. Then, when I run the code a third time, I get the correct plot once again. Continuing to rerun my code repeats the cycle of good plot -> bad plot -> good plot. Any ideas on whats causing this?

Sub CreatingChartOnChartSheet()

Dim ch As Chart

Dim xrng As Range
Dim yrng1 As Range
Dim yrng2 As Range

Set ch = Charts.Add
Set xrng = Sheets("Power").Range("A2:A65536")
Set yrng1 = Sheets("Power").Range("D2:D65536")
Set yrng2 = Sheets("Power").Range("E2:E65536")

With ch

    ' If there is a previous chart, delete it
    For Each Chart In ActiveWorkbook.Charts
    If Chart.Name = "Power Chart" Then
        Application.DisplayAlerts = False
        Charts("Power Chart").Delete
        Application.DisplayAlerts = True
    End If
    Next Chart        
    
    .SeriesCollection.NewSeries
    .SeriesCollection(1).Name = "=""Series 1"""
    .SeriesCollection(1).XValues = xrng    
    .SeriesCollection(1).Values = yrng1
    
    .SeriesCollection.NewSeries
    .SeriesCollection(2).Name = "=""Series 2"""
    .SeriesCollection(2).XValues = xrng
    .SeriesCollection(2).Values = yrng2
    
    .SetElement (msoElementChartTitleAboveChart)
    .Name = "Power Chart"
    .ChartTitle.Text = "Power"
    .SetElement (msoElementLegendRight)
    .Axes(xlCategory, xlPrimary).HasTitle = True
    .Axes(xlCategory, xlPrimary).AxisTitle.Text = "Time (h)"
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Text = "Power (kW)"
    '.Axes(xlCategory).MajorUnit = 1
    '.Axes(xlCategory).MinorUnit = 1
    
End With

End Sub

Answer 1

If you insert a chart sheet or chartobject while there's data selected on a worksheet, the chart will auto-plot the selected data. Whenever adding a chart via VBA it's a good practice to first delete any series which got auto-added.

Sub CreatingChartOnChartSheet()

    Dim xrng As Range
    Dim yrng1 As Range
    Dim yrng2 As Range
    
    With Sheets("Power")
        Set xrng = .Range("A2:A65536")
        Set yrng1 = .Range("D2:D65536")
        Set yrng2 = .Range("E2:E65536")
    End With

    Application.DisplayAlerts = False
    On Error Resume Next
    Charts("Power Chart").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True
    
    With Charts.Add
        'remove any auto-plotted data
        Do While .SeriesCollection.Count > 0
            .SeriesCollection(1).Delete
        Loop
        
        With .SeriesCollection.NewSeries
            .Name = "Series 1"
            .XValues = xrng
            .Values = yrng1
        End With
        
        With .SeriesCollection.NewSeries
            .Name = "=""Series 2"""
            .XValues = xrng
            .Values = yrng2
        End With
        
        .SetElement msoElementChartTitleAboveChart
        .Name = "Power Chart"
        .ChartTitle.Text = "Power"
        .SetElement (msoElementLegendRight)
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Text = "Time (h)"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Text = "Power (kW)"
        '.Axes(xlCategory).MajorUnit = 1
        '.Axes(xlCategory).MinorUnit = 1
        
    End With

End Sub