VBA: Create Line Chart with unknonw ending source

[dannijs]

I am working on a project that I need to create a Line Chart from the data that the user enter weekly.
This is what I've got so far:

' Graph
ActiveSheet.ChartObjects("Comparison").Activate
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).name = "=""Actual Sale"""
ActiveChart.SeriesCollection(1).Values = "='Product'!$B$8:$B$1008"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).name = "=""Alpha 0.10"""
ActiveChart.SeriesCollection(2).Values = "='Product'!$AB$8:$AB$1008"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(3).name = "=""Alpha 0.25"""
ActiveChart.SeriesCollection(3).Values = "='Product'!$AC$8:$AC$1008"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(4).name = "=""Alpha 0.50"""
ActiveChart.SeriesCollection(4).Values = "='Product'!$AD$8:$AD$1008"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(5).name = "=""Alpha 0.90"""
ActiveChart.SeriesCollection(5).Values = "='Product'!$AE$8:$AE$1008"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(6).name = "=""Beta 0.10"""
ActiveChart.SeriesCollection(6).Values = "='Product'!$AX$8:$AX$1008"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(7).name = "=""Beta 0.25"""
ActiveChart.SeriesCollection(7).Values = "='Product'!$BI$8:$BI$1008"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(8).name = "=""Beta 0.50"""
ActiveChart.SeriesCollection(8).Values = "='Product'!$BT$8:$BT$1008"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(9).name = "=""Beta 0.90"""
ActiveChart.SeriesCollection(9).Values = "='Product'!$CE$8:$CE$1008"

However, I really have no clue on how to set the ending cell because the user will continuously enter data week by week, thus the last cell is unknown.
Thanks very much in advance

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

I think the easiest way would be to turn your data into an Excel Table. To do this, select a cell within the data area, go to the Insert menu, pick Tables then Table. You will be asked to confirm the table range and whether you are using header rows.

I am not sure if you will need to re-create your chart. Excel may just pick up the new table. If not you may have to re-create it.

From now on, every time you add a row to your Table, the chart should update itself automatically with the extra row.