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 codes 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

'Also tried other similar macros find on different sites:
'------------------------------------------------
Sub Test1()

Dim ChData As Range
Dim LR     As Long
LR = Columns("A:G").Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
Set ChData = Sheets("Chart").Range(Cells(4, 1), Cells(LR, 7))

    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.PlotArea.Select
    Application.CutCopyMode = False

    With Sheets("Chart")
        ActiveChart.SetSourceData Source:=.Range(Cells(4, 1), Cells(LR, 7))  'runtime error 445, object doesn't support this action
    End With
End Sub
'------------------------------------------------
Sub Test2()

Dim ws As Worksheet
Dim chto As ChartObject
Dim cht As Chart
Dim LR     As Long
LR = Columns("A:G").Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row

    Set ws = ThisWorkbook.Worksheets("Chart")
    Set chto = ws.ChartObjects("Chart 1")
    Set cht = chto.Chart
    'cht.SetSourceData Source:=ws.Range("A4:G24")                   'runtime error 445, object doesn't support this action
    cht.SetSourceData Source:=ws.Range(Cells(4, 1), Cells(LR, 7))   'runtime error 445, object doesn't support this action
End Sub
'------------------------------------------------
Sub Test3()

Dim ChData As Range
Dim LR          As Long
LR = Columns("A:G").Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
Set ChData = Sheets("Chart").Range(Cells(4, 1), Cells(LR, 7))
ChData.Select
    
    'With ActiveSheet
        'On Error Resume Next                                                       'With "On Error": "Chart 1" is updated with new "Source" range!
            ActiveSheet.ChartObjects("Chart 1").Chart.SetSourceData Source:=ChData  'Without "On Error": runtime error 445, object doesn't support this action
            '.ChartObjects("Chart 1").Chart.SetSourceData Source:=ChData            '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.