CODEWITHSUNDEEP
excelvba
whitebear
whitebear

Reputation: 12461

Excel VBA for making multiple graph without using Range

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?

Upvotes: 0

Views: 304

Answers (2)

Dy.Lee
Dy.Lee

Reputation: 7567

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

Upvotes: 1

teylyn
teylyn

Reputation: 35990

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.

Upvotes: 0

Related Questions