How to change the range of a chart in Excel using VBA?

I'm using an Excel sheet to keep track of a particular time series (my daily weight, if you must know). I creat开发者_开发百科ed a macro that inserts a row, automatically adds today's date and calculates a moving average based on my input.

There is also a chart to visualize my progress. I have tried recording a macro that updates the time series in the graph, but to no success.

How can I create a macro or VBA script that, when executed, updates the range of the graph from A(x):Cy to A(x-1):Cy to include today's measurement?

Thanks!

Use the chart.SetSourceData method and use the range that you calculated during the insertion of your new row.