0

I want to have multiple series graph.

I made this code to show three lines.

However, it doesn't work, I can't use Range because my data source is sparse at three line ("I1:I30","I51:I80","I101:I131")

With ActiveSheet.Shapes.AddChart.Chart
    .HasTitle = True
    .ChartTitle.Text = "My Graph"
    .ChartType = xlLine
    .SetSourceData Range("I1:I30","I51:I80","I101:I131") 'thats the data for three lines I want to show.
    .SeriesCollection(1).Name = "item1" 
    .SeriesCollection(1).XValues = Range("G1:G30") 

How can I solve this?

2 Answers 2

1

The range setting is wrong.

Range("I1:I30","I51:I80","I101:I131")

The range should be set as follows.

Range("I1:I30, I51:I80, I101:I131")

However, if the range of the x-axis is constant, it will be appropriate to create a scatter chart.

Sub test()
    Dim sourRange(1 To 3) As Range
    Dim Srs As Series
    Dim Cht As Chart
    Dim i As Integer
    
    Set sourRange(1) = Range("i1:i30")
    Set sourRange(2) = Range("i51:i80")
    Set sourRange(3) = Range("i101:i131")
    
    Set Cht = ActiveSheet.Shapes.AddChart.Chart
    With Cht
        .HasTitle = True
        .ChartTitle.Text = "My Graph"
        .ChartType = xlXYScatterLinesNoMarkers
        For Each Srs In .SeriesCollection
            Srs.Delete
        Next Srs
        For i = 1 To 3
            Set Srs = .SeriesCollection.NewSeries
            With Srs
                .Name = "item" & i
                .Values = sourRange(i)
                .XValues = Range("G1:G30")
            End With
        Next i
    End With
End Sub
Sign up to request clarification or add additional context in comments.

Comments

0

If the data is not in one contiguous range, then you need to add each series separately. It's the same when creating a chart manually with data in different parts of the worksheet. You cannot select a non-contiguous range and then insert a chart. Instead, create the chart with just one series, then use additional, separate commands to add more series to the chart.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.