Update Chart SourceData in dynamic range

[soidog]

Hi all,
I am using Excel 2016 and want to update a "Box and Whisker" chart with new "Source Data" in a dynamic range.
One workaround is to delete "Chart 1" and create a new "Box and Whisker" chart but how to update existing chart?
I have tried below code and others but I can't get it to work.

Sub ChartData1()
Dim ChartRange1 As Range
Dim LR          As Long
LR = Columns("A:G").Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row

    With ActiveSheet.ChartObjects("Chart 1").Chart.ChartArea
        'On Error Resume Next                                                    'With "On Error": "Chart 1" is updated with new "Source" range!
            ActiveChart.SetSourceData Source:=Range(Cells(4, 1), Cells(LR, 7))   'Without "On Error": runtime error 445, object doesn't support this action
        'On Error GoTo 0
    End With
End Sub

Any help is much appreciated.

 

[Jon Peltier]

Replying to the first post in the thread.

This code should work better:

Sub UpdateChartData()
    Dim LR As Long
    LR = ActiveSheet.Columns("A:G").Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row

    ActiveChart.SetSourceData Source:=ActiveSheet.Range(ActiveSheet.Cells(4, 1), ActiveSheet.Cells(LR, 7))
End Sub

 

[DanteAmor]

Of course. Try this please.

Sub ChartData1()
  Dim lr          As Long
  lr = Columns("A:G").Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
  ActiveSheet.ChartObjects("Chart 1").Activate
  ActiveChart.SetSourceData Source:=activesheet.Range("$A$4:$G$" & lr)
End Sub

 

[soidog]

Jon Peltier, DanteAmor:


When I click in chart, range("A4:G15") is selected. Every cell in that range has a number.
Then I add a number in range("A16") and run your codes.


Jon Peltier:
I changed your code to:

Sub UpdateChartData()
    Dim LR As Long
    LR = ActiveSheet.Columns("A:G").Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).row
    ActiveSheet.ChartObjects("Chart 1").Activate    'added by soidog
    ActiveChart.SetSourceData Source:=ActiveSheet.range(ActiveSheet.Cells(4, 1), ActiveSheet.Cells(LR, 7))
End Sub

Both your codes still gives me: "runtime error 445, Object doesn't support this action", at last line in code.
If I click "Debug" in error popup window, chart is updated to the new SourceData range: range("A4:G16")!
If I click "End", chart is not updated.

Thanks

 

[Jon Peltier]

I don't know why you're seeing that error. The code runs fine here, and it looks like it should run fine. There may be something funky in your installation.

 

[soidog]

Jon Peltier, DanteAmor:

Until now I have only tested your codes in a "Box & Whisker" chart.
But today when I changed to some other chart types, like "Column", "Line", and "Pie", and run your codes it works!
I also tried the "Waterfall" chart but that didn't work. I have not tested all of the chart types.
So my question to you is: Have you run your code in a "Box & Whisker" chart without errors?


Thanks

 

[Jon Peltier]

I did not try a waterfall chart or any of the conventional charts. But I did try a box and whisker chart, since you specifically mentioned it. And the code worked fine, without the error you report.

 

[soidog]

That is so strange. I guess it must be something in my Excel 2016 settings.
For the moment, I give up! I have already spent too much time on this topic and I thank both of you for trying to help me.
I will continue with creating a new chart each time I need an update of "SourceData".
I will keep the thread open and hopefully someone else can help me.


Thanks again

 

[DanteAmor]

Youre welcome!