0

I am trying to create a xy plot where the x values is the time axis (column A) and the y values are in the other columns. (in the example only C and D) When I try my code from below, I get a graph with time axis (so column A) and y values all the other columns (B, C, D, E,...) which is unwanted.

I can see that the .seriescollection(1) and (2) overwrite the default y-values (because my range is smaller), but all the others (column B, D, E,...) still remains in the graph.

Any thoughts why? Thanks in advance!

    Sub grafieken()
'
' grafieken Macro
'
Dim sh As Worksheet
Dim chrt As Chart
Dim naaaam As String

naaam = ActiveWorkbook.ActiveSheet.Name

Set sh = ActiveWorkbook.Worksheets(naaam)
Set chrt = sh.Shapes.AddChart.Chart
With chrt
    'Data?
    .ChartType = xlXYScatter
    .SeriesCollection.NewSeries
    .SeriesCollection(1).Name = sh.Range("$C$1")
    .SeriesCollection(1).XValues = sh.Range("$A$2:$A$11")
    .SeriesCollection(1).Values = sh.Range("$C$2:$C$11")
    .SeriesCollection(2).Name = sh.Range("$D$1")
    .SeriesCollection(2).XValues = sh.Range("$A$2:$A$11")
    .SeriesCollection(2).Values = sh.Range("$D$2:$D$11")

    'Titles?
    .HasTitle = True
    .ChartTitle.Characters.Text = naaam

End With

End Sub

1 Answer 1

1

For me it worked when I added the SetSourceData:

Sub grafieken()
Dim sh As Worksheet
Dim chrt As Chart
Dim naaaam As String

naaam = ActiveWorkbook.ActiveSheet.Name

Set sh = ActiveWorkbook.Worksheets(naaam)
Set chrt = sh.Shapes.AddChart.Chart
With chrt
    'Data?
    .SetSourceData Source:=Range("Sheet1!$C$1:$D$11")
    .ChartType = xlXYScatter
    .SeriesCollection(1).Name = sh.Range("$C$1")
    .SeriesCollection(1).XValues = sh.Range("$A$2:$A$11")
    .SeriesCollection(1).Values = sh.Range("$C$2:$C$11")
    '.SeriesCollection.NewSeries ' removed
    .SeriesCollection(2).Name = sh.Range("$D$1")
    .SeriesCollection(2).XValues = sh.Range("$A$2:$A$11")
    .SeriesCollection(2).Values = sh.Range("$D$2:$D$11")

    'Titles?
    .HasTitle = True
    .ChartTitle.Characters.Text = naaam
End With
End Sub

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .