Using VBA to create Chart with non-contiguous arrays

kdd2156

New Member
Joined
Aug 4, 2016
Messages
2
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.

Code:
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
Code:
 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.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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. ??
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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:

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,133,244
Messages
5,657,587
Members
418,401
Latest member
B_A_M155

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top