Charts in VBA

[SBF12345]

I am working on constructing charts with VBA within a For loop. There are two main problems I am facing.

The first iteration generates a chart with five data series despite my intention of including only two. One is a multiple range source, and the other two are 1 values. On the second third, etc iterations the charts include 3 series despite my intention to include only two. The extra series on subsequent iterations is a 1 value. I am not positive of the source of cause for these additional data series or how to track down the source.

The second is in production of two y axis. Both y axis are showing labels, but one of the series is disproportional in terms of its y axis rate of change.

I suspect that the two main problems are related and that the y axis dis-proportionality results from the inclusion of the unwanted 3rd series (series ={1}) in the second, third iterations. And similarly with the first iteration inclusion of the three extra series.

Any ideas as to how to remove the extra series? I didn't intend to write in these additional series. The code is below:

Set sh = Worksheets("LowDistCharts")    Set chrt = sh.ChartObjects.Add(0, 0, 300, 300)
    Set ch = chrt.Chart
    
        With chrt
            .Height = 300
            .Width = 300
            .Top = 1 + ((aa - 4) * 300)
            .Left = 1
        End With
        
        With ch
            .HasTitle = True
            .ChartTitle.Text = aa & " " & StartDate & " to " & EndDate
            .ChartTitle.Font.Size = 8
            .ChartType = xlLine
            .SeriesCollection.NewSeries
            .SeriesCollection(1).Values = ActiveWorkbook.Worksheets("ActiveSheet").Range(RngXR, RngXR2)
            '.SeriesCollection(1).AxisGroup = 1
            .SeriesCollection.NewSeries
            .SeriesCollection(2).Values = ActiveWorkbook.Worksheets("ActiveSheet").Range(RngStartR, RngEndR)
            .SeriesCollection(2).AxisGroup = 2
            .HasLegend = False
        End With

 

[SBF12345]

Update: I added the line:

.seriescollection(3).delete

The result is as follows:

The first iteration still includes a "series 4" in the chart. Addition of the single line above seems to have had the effect of removing the 3rd and 5th series in the output from the first iteration. Subsequent iterations include two plotted series as I intended.

The remaining problem seems to be related to the first iteration only. The remaining "series4" data series plotted in the chart has a value of "={1}". Why would there be a series included in only the first series and how to remove it?

 

[SBF12345]

I added a short loop so the code reads as follows:

Set sh = Worksheets("LowDistCharts")    Set chrt = sh.ChartObjects.Add(0, 0, 300, 300)
    Set ch = chrt.Chart
    
    Do While ch.SeriesCollection.Count > 1
    
        ch.SeriesCollection(1).Delete
    
    Loop
    
        With chrt
            .Height = 300
            .Width = 300
            .Top = 1 + ((aa - 4) * 300)
            .Left = 1
        End With
        
        With ch
            .HasTitle = True
            .ChartTitle.Text = aa & " " & StartDate & " to " & EndDate
            .ChartTitle.Font.Size = 8
            .ChartType = xlLine
            .SeriesCollection.NewSeries
            .SeriesCollection(1).Values = ActiveWorkbook.Worksheets("ActiveSheet").Range(RngXR, RngXR2)
            .SeriesCollection.NewSeries
            .SeriesCollection(2).Values = ActiveWorkbook.Worksheets("ActiveSheet").Range(RngStartR, RngEndR)
            .SeriesCollection(2).AxisGroup = 2
            .SeriesCollection(3).Delete
            .HasLegend = False
        End With