chart series data in first iteration of for loop vba

Question

I am having an additional data series included in a chart that I don't intend to include. It is only showing up on the first iteration and no others. The value of the series is "={1}". How did this data series come to be included in the chart and how can it be removed?

 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
        .SeriesCollection(3).Delete
        .HasLegend = False
    End With

Tim Williams · Accepted Answer · 2018-11-11 02:09:24Z

1

When adding a chart you may find one or more series "auto-added" if there are cells with data selected when the chart is created.

You can make sure these are removed before adding the data you want:

Set chrt = sh.ChartObjects.Add(0, 0, 300, 300)
Set ch = chrt.Chart

Do While ch.SeriesCollection.Count > 1
    ch.SeriesCollection(1).Delete
Loop

answered Nov 11, 2018 at 2:09

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

chart series data in first iteration of for loop vba

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related