Automatically Rebuilding a Chart When the Data Range Changes with Excel VBA

Continuing to enhance the example related to the activity of computer clubs, let us improve the automatic chart construction. Now, the user can add or delete any number of months in the overall report table located on the Vedomost worksheet. After changing the number of months, it is sufficient to click the club list, and the chart will automatically be rebuilt.

To implement this task, modify the Click event procedure of the Club list as shown:

Controlling chart type and legend. Vedomost worksheet module

Private Sub Club_Click()
    Dim r As Integer
    ActiveSheet.ChartObjects(1).Activate
    r = Club.ListIndex + 1
    Dim rgn As Range
    Dim rgnTitle As Range
    Set rgn = Range("A3").CurrentRegion
    Set rgnTitle = rgn.Rows(1)
    Set rgnTitle = rgnTitle.Offset(0, 1)
    Set rgnTitle = rgnTitle.Resize(ColumnSize:=rgnTitle.Columns.Count - 2)
    Set rgn = rgn.Offset(1, 1)
    Set rgn = rgn.Resize(rgn.Rows.Count - 2, rgn.Columns.Count - 2)
    With ActiveChart
        .SetSourceData Source:=rgn.Rows(r), PlotBy:=xlRows
        .SeriesCollection(1).XValues = rgnTitle
    End With
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = Club.Text
    End With
End Sub