excel - VBA: Modify chart data range

Question

My "Chart data range" is ='sheet1'!$A$1:$Z$10. I'd like to make a VBA macro (or if anybody knows a formula I can use, but I couldn't figure one out) to increase the ending column of the range for chart1 by 1 every time I run the macro. So essentially:

chart1.endCol = chart1.endCol + 1

What is the syntax for this using ActiveChart or is there a better way?

Answer 1

Offset function dynamic range makes it possible.

Sample data

Steps

• Define a dynamic named range =OFFSET(Sheet1!$A$2,,,1,COUNTA(Sheet1!$A$2:$Z$2)) and give it a name mobileRange
• Right Click on Chart
• Click on Select Data

This screen will come

Click on Edit under Legend Entries.(mobiles is selected)

• change the Series value to point to mobileRange named range.
• Now if data for future months are added to mobile sales it will automatically reflect in chart.