Problem setting chart source

[mcomp72]

I am helping a friend update an Excel file made by someone else, and it uses charts. I don't have much experience with them, so am having a hard time figuring out how to use VBA to change the source data.

The existing chart data is using a fixed last row, meaning if there is ever any data that goes beyond that row, it is ignoring it. Here's a screenshot of the chart source information that currently exists.

You can see that the last row will always be 772. But sometimes the data may extend beyond that row. I know how to write code to get the last row with data in it. But I'm having trouble updating the chart data range.

Here is the code I've written to do it.

Set Rng = ThisWorkbook.Sheets("Net Results").Range("$A$3:$A$" & LR & ",$D$3:$D$" & LR)
Charts("Drawdown").SetSourceData Source:=Rng, PlotBy:=xlColumns

I am getting Run-time error 9: Subscript out of range on the 2nd line of code. I can't figure out why. The chart name is correct. LR is the variable that holds the last row. By stepping through the code, I have confirmed that its value is correct. Any ideas what might be wrong?

 

[Asbestos_Jen]

For charts with a data source that may grow over time, I used named ranges in my series, like so:

 

[mcomp72]

For charts with a data source that may grow over time, I used named ranges in my series, like so:
View attachment 106183

I appreciate the reply, but I'd really love to know why the code isn't working. I need to learn about how to update charts via VBA, so understanding what I've done wrong in the code would be great.

 

[mcomp72]

I was able to figure it out, finally. Instead of the two lines of code I listed in my original post, I used the below code and it worked.

Set Rng = ThisWorkbook.Sheets("Net Results").Range("$A$3:$A$" & LR & ",$D$3:$D$" & LR)
ThisWorkbook.Sheets("SummaryCharts").ChartObjects("Drawdown").Activate
ActiveChart.SetSourceData Source:=Rng

I don't know if this is the best way to do it (I've heard you should avoid using "Activate" in VBA code) but since it worked, I'll go with it unless anyone has any other suggestions.