How to automate making charts for every N number of cells and rows in Excel?

Question

I am working with a large data set that contains around 17k values and I need to make a chart for every 29 values. For instance if the data set is of 58 values, I need to make a chart for every 29 values and thus make two charts.

I have written the following code to make the charts:

'''

Sub Charts()
'
' Charts Macro
'

'
    Range("D3:D32").Select
    ActiveSheet.Shapes.AddChart2(227, xlLine).Select
    ActiveChart.SetSourceData Source:=Range("Sheet1!$D$3:$D$32")
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    ActiveChart.FullSeriesCollection(1).Name = "=""Marginal Costs"""
    ActiveChart.FullSeriesCollection(1).XValues = "=Sheet1!$C$3:$C$32"
     Range("D32:D61").Select
    ActiveSheet.Shapes.AddChart2(227, xlLine).Select
    ActiveChart.SetSourceData Source:=Range("Sheet1!$D$32:$D$61")
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    ActiveChart.FullSeriesCollection(1).Name = "=""Marginal Costs"""
    ActiveChart.FullSeriesCollection(1).XValues = "=Sheet1!$C$32:$C$61"
End Sub

'''

This is quite a tedious step for 17k values.

Can someone please suggest an easier way of making charts for the data? I would be deeply grateful for the same.

Answer 1

Use a For/Next Loop with Step size of 29.

Option Explicit

Sub ChartsMacro()
  
    Const SIZE = 29
    
    Dim ws As Worksheet, cht As Chart, c, sTitle As String
    Dim lastrow As Long, r As Long, n As Long, L As Long, T As Long
    Dim t0 As Single: t0 = Timer
    
    Application.ScreenUpdating = False
    Set ws = ActiveSheet
    With ws
  
        lastrow = .Cells(.Rows.Count, "C").End(xlUp).Row
        For r = 3 To lastrow Step SIZE
            n = n + 1
            Application.StatusBar = "Chart " & n
            
            ' position and title chart
            sTitle = "Chart " & n & " (" & r & " to " & r + SIZE & ")"
            L = .Range("F" & r + 1).Left
            T = .Range("F" & r + 1).Top
            .Range("C" & r).Resize(, 10).Borders(xlEdgeTop).Color = vbBlack
            ' create chart
            Set cht = .Shapes.AddChart2(227, xlLine, Left:=L, Top:=T).Chart
            cht.SetSourceData Source:=.Range("D" & r).Resize(SIZE + 1)
            cht.ChartTitle.Text = sTitle
            cht.FullSeriesCollection(1).Name = "=""Marginal Costs"""
            cht.FullSeriesCollection(1).XValues = .Range("C" & r).Resize(SIZE + 1)
        Next
    End With
    Application.ScreenUpdating = True
    Application.StatusBar = "Done"

    MsgBox n & " Charts created in " & Format(Timer - t0, "0.0") & " secs"
End Sub

Answer 2

Nested for loop is how I've handled similar problems in the past. Something like

For i=1 to 17k/29
     create chart
     for k=1 to 29
         range = i*k
         data = range

Assuming your data is sequential and in same range each time you just have to figure out the actual steps for a single set then let it go.