Adding chart with vba but it adds too many series

[jcuoco78]

I am having an issue with adding a chart to an excel sheet with vba. I create the chart with vba and add it to the worksheet first, and then I add it to a userform image box later on. The issue that I have is that when vba creates the chart based on my data, it adds a bunch of other series to the chart, although they do not show up as lines on the graph. They do however show in the series legend when I go to select data, and the legend on the graph itself only shows 2 of the 8 series that I want on the graph because it has them all spaced out because there are so many. It shows series of the columns that I did not select for the chart and also the same ones a second time bit they look different. I don't want to have to add code to remove the extra series' but that is the only way I know how to fix it myself. Any thoughts?

Also if anyone knows a better way to grab the selected data that will shorten the code up I am open to suggestions! This is just pasted into a module for me to work through right now. I have a userform with much more code in it to do various functions that this will become part of later on.

Thanks in advance.

Sub Macro6()

Application.ScreenUpdating = False

Dim ws As Worksheet
Dim MyChart As Chart
Dim ChartName As String
Dim Lastrow As Integer

Set ws = Worksheets("Moving Range")
ws.Select
Lastrow = Columns("A").Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).row

Set MyChart = ActiveSheet.Shapes.AddChart(xlXYScatterLines).Chart

        MyChart.SeriesCollection.NewSeries
        MyChart.SeriesCollection(1).Name = Range("$E$1")
        MyChart.SeriesCollection(1).Values = Range("E2:E" & Lastrow)
        MyChart.SeriesCollection(1).XValues = ActiveSheet.Range("$A:$A")
            With MyChart
                .ChartArea.Height = 288
                .ChartArea.Width = 510
            End With
            MyChart.SeriesCollection(1).Select
            With Selection
                .MarkerStyle = 2
                .MarkerSize = 5
            End With
            With Selection.Format.Fill
                .Visible = msoTrue
                .ForeColor.RGB = RGB(0, 112, 192)
                .Solid
            End With
            With Selection.Format.Line
                .Visible = msoTrue
                .ForeColor.RGB = RGB(0, 112, 192)
            End With
            With Selection.Format.Line
                .Visible = msoTrue
                .Weight = 1
            End With
        MyChart.SeriesCollection.NewSeries
        MyChart.SeriesCollection(2).Name = Range("$L$1")
        MyChart.SeriesCollection(2).Values = Range("L2:L" & Lastrow)
        MyChart.SeriesCollection(2).Select
            Selection.MarkerStyle = -4142
            With Selection.Format.Line
                .Visible = msoTrue
                .ForeColor.RGB = RGB(255, 0, 0)
            End With
        MyChart.SeriesCollection.NewSeries
        MyChart.SeriesCollection(3).Name = Range("$M$1")
        MyChart.SeriesCollection(3).Values = Range("M2:M" & Lastrow)
        MyChart.SeriesCollection(3).Select
            Selection.MarkerStyle = -4142
            With Selection.Format.Line
                .Visible = msoTrue
                .ForeColor.RGB = RGB(255, 0, 0)
            End With
        MyChart.SeriesCollection.NewSeries
        MyChart.SeriesCollection(4).Name = Range("$N$1")
        MyChart.SeriesCollection(4).Values = Range("N2:N" & Lastrow)
        MyChart.SeriesCollection(4).Select
            Selection.MarkerStyle = -4142
            With Selection.Format.Line
                .Visible = msoTrue
                .ForeColor.RGB = RGB(112, 48, 160)
            End With
        MyChart.SeriesCollection.NewSeries
        MyChart.SeriesCollection(5).Name = Range("$O$1")
        MyChart.SeriesCollection(5).Values = Range("O2:O" & Lastrow)
        MyChart.SeriesCollection(5).Select
            Selection.MarkerStyle = -4142
            With Selection.Format.Line
                .Visible = msoTrue
                .ForeColor.RGB = RGB(112, 48, 160)
            End With
        MyChart.SeriesCollection.NewSeries
        MyChart.SeriesCollection(6).Name = Range("$P$1")
        MyChart.SeriesCollection(6).Values = Range("P2:P" & Lastrow)
        MyChart.SeriesCollection(6).Select
            Selection.MarkerStyle = -4142
            With Selection.Format.Line
                .Visible = msoTrue
                .ForeColor.RGB = RGB(0, 176, 80)
            End With
        MyChart.SeriesCollection.NewSeries
        MyChart.SeriesCollection(7).Name = Range("$Q$1")
        MyChart.SeriesCollection(7).Values = Range("Q2:Q" & Lastrow)
        MyChart.SeriesCollection(7).Select
            Selection.MarkerStyle = -4142
            With Selection.Format.Line
                .Visible = msoTrue
                .ForeColor.RGB = RGB(0, 176, 80)
            End With
        MyChart.SeriesCollection.NewSeries
        MyChart.SeriesCollection(8).Name = Range("$R$1")
        MyChart.SeriesCollection(8).Values = Range("R2:R" & Lastrow)
        MyChart.SeriesCollection(8).Select
            Selection.MarkerStyle = -4142
            With Selection.Format.Line
                .Visible = msoTrue
                .ForeColor.RGB = RGB(0, 0, 0)
            End With

    ActiveChart.ChartArea.Select
    Application.ScreenUpdating = True
    
End Sub

 

[Domenic]

After creating the chart, but before creating new series, delete the extra series...

[color=green]'[/color]
'
'

[color=darkblue]Set[/color] MyChart = ActiveSheet.Shapes.AddChart(xlXYScatterLines).Chart

[color=darkblue]With[/color] MyChart
    [color=darkblue]Do[/color] [color=darkblue]While[/color] .SeriesCollection.Count > 0
        .SeriesCollection(1).Delete
    [color=darkblue]Loop[/color]
[color=darkblue]End[/color] [color=darkblue]With[/color]

MyChart.SeriesCollection.NewSeries
[color=green]'[/color]
'
'

Hope this helps!

 

[jcuoco78]

Can't believe it was that simple! Thanks a bunch!

 

[Domenic]

You're very welcome!