Using VBA to create Chart with non-contiguous arrays

[kdd2156]

I'm trying to create a VBA macro which generates multiple line graphs. I'm having an issue with the selection of the graph due to the iteration through each row.

Sub GenerateCharts()


        Dim i As Integer
        NumRows = Range("B3", Range("B3").End(xlDown)).Rows.Count
        NumRows = NumRows + 2
        For i = 3 To NumRows
                    ActiveSheet.Shapes.AddChart.Select
                    ActiveChart.SetSourceData Source:=Sheets("Mike").Range("B2:T2", Range(Cells(i, 2), Cells(i, 2).End(xlToRight)).Select)
                    ActiveChart.ChartType = xlLineMarkers
                        With ActiveChart
                            .HasTitle = True
                            .ChartTitle.Characters.Text = (A1)
                            .Axes(xlCategory, xlPrimary).HasTitle = True
                            .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Month"
                            .Axes(xlCategory, xlPrimary).CategoryType = xlTimeScale
                            .Axes(xlCategory, xlPrimary).TickLabels.NumberFormat = "mmm-yy"
                            .Axes(xlValue, xlPrimary).HasTitle = True
                            .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Efficiency"
                            .Axes(xlValue, xlPrimary).TickLabels.NumberFormat = "0.0%"
                            .HasAxis(xlCategory, xlPrimary) = True
                            .HasAxis(xlValue, xlPrimary) = True
                            .HasDataTable = False
                        End With
                        Cells(1, 1).Select
       Next i
End Sub

I think my issue lies here

 ActiveChart.SetSourceData Source:=Sheets("Mike").Range("B2:T2", Range(Cells(i, 2), Cells(i, 2).End(xlToRight)).Select)

I've changed it multiple times but I can't get a successful result. Basically, {B2:T2} is the row header and is the same for each chart. The second half is the part I'm having an issue getting to work. Basically, I want Row 3 to plot, then Row 4, etc. I'm sure i'm probably missing something basic but I can't figure it out. The only success I've had is where it would select header and row 3, then header, Row 3, and row4, etc. I just want two rows selected each time.

 

[pgc01]

Hi

Can you clarify how you get the NumRows?

The charts data are in the worksheet "Mike", but you are getting the NumRows from the active worksheet. ??

 

[kdd2156]

The only sheet in workbook is "Mike". I just got lazy when naming.

 

[pgc01]

OK

I assumed that both the data and the charts are in the worksheet "Mike".

The code could, however, be called from another worksheet if there were more worksheets in the workbook.

Remarks:
- I named the charts so that we know to which row of data the chart refers to
- Some variable declarations were missing
- I used your code and changed very little

Try:

Sub GenerateCharts()
Dim i As Long
Dim NumRows As Long
Dim cht As Chart

    With Worksheets("Mike")
        NumRows = .Range("B3", .Range("B3").End(xlDown)).Rows.Count
    End With
    NumRows = NumRows + 2
    
    For i = 3 To NumRows
        
        With Worksheets("Mike")
            Set cht = .Shapes.AddChart.Chart
            cht.SetSourceData Source:=Application.Union(.Range("B2:T2"), .Range(.Cells(i, 2), .Cells(i, 2).End(xlToRight)))
        End With
        
        With cht
            .Parent.Name = "Chart Row " & i
            .ChartType = xlLineMarkers
            
            .HasTitle = True
            .ChartTitle.Characters.Text = "Title text"
            
            .HasAxis(xlCategory, xlPrimary) = True
            With .Axes(xlCategory, xlPrimary)
                .HasTitle = True
                .AxisTitle.Characters.Text = "Month"
                .CategoryType = xlTimeScale
                .TickLabels.NumberFormat = "mmm-yy"
            End With
            
            .HasAxis(xlValue, xlPrimary) = True
            With .Axes(xlValue, xlPrimary)
                .HasTitle = True
                .AxisTitle.Characters.Text = "Efficiency"
                .TickLabels.NumberFormat = "0.0%"
            End With
            
            .HasDataTable = False
        End With
    Next i
End Sub