Thread: Changing chart range - VBA

Hi,

Does anybody know how to change the source range of chart? Using VBA.

For instace...

Initially I have:
Green Yellow Red Purple
1 6 4 2
2 5

And I generate the chart:

Sub Macro1()

    NbItems = Range("Sheet1!K7")
    NbPoints = Range("Sheet1!K8")
    Sheets("Sheet1").Activate
    
    Set rg1 = Range(Cells(1, 1), Cells(NbPoints + 1, NbItems + 1))
    rg1.Select
   
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlColumnStacked
    ActiveChart.PlotBy = xlColumns
    ActiveChart.ApplyDataLabels ShowSeriesName:=True, ShowValue:=False
   
End Sub

Later one, I have:
Green Yellow Red Purple Pink
1 6 4 2
2 5 2

How can I update the same chart? I.e., adding 2, Pink 2 to the existing chart (Using VBA).

Any help is welcome. :-)
Check the files.

Sub AddNewData()
    
    Dim rngData As Range
    
    Set rngData = Range("F1")
    With ActiveSheet.ChartObjects(1)
        With .Chart.SeriesCollection.NewSeries
            .Name = rngData
            .Values = rngData.Offset(1, 0).Resize(2, 1)
        End With
    End With
End Sub