VBA Clear Chart Dynamically and refresh with new values

[jammerules]

Hello,

I have a chart named "VelChart". It fetches data from a set of dynamically created cell values. However, I want the chart to be cleared of any graph every time I said referenced cell values are refreshed.

I have this code that isn't working:

        Worksheets("Velocity Tracker").ChartObjects("VelChart").Activate
        ActiveChart.ChartArea.ClearContents

When I use the above code, it is deleting the chart altogether. And when I try to reference the chart elsewhere in the code, it throws an error. How do I just clear the chart contents and not delete the chart altogether?


This is the code that I have elsewhere that I am referencing:

Public Function DrawVelChart(strComponent As String)
    Select Case strComponent
        Case "Infrastructure (CI/CD/DevOps)"
            Worksheets("Velocity Tracker").ChartObjects("VelChart").Activate
            ActiveChart.SetSourceData Source:=Worksheets("Velocity Tracker").Range("C6:E" & iLastRow)
            ActiveChart.ChartTitle.Text = "Infra Velocity Trend"
            ActiveChart.SeriesCollection(1).XValues = Worksheets("Velocity Tracker").Range("G6:G" & iLastRow)
        Case "Configuration"
            Worksheets("Velocity Tracker").ChartObjects("VelChart").Activate
            ActiveChart.SetSourceData Source:=Worksheets("Velocity Tracker").Range("C6:E" & iLastRow)
            ActiveChart.ChartTitle.Text = "Configuration Velocity Trend"
            ActiveChart.SeriesCollection(1).XValues = Worksheets("Velocity Tracker").Range("G6:G" & iLastRow)
        Case "Analytics"
            Worksheets("Velocity Tracker").ChartObjects("VelChart").Activate
            ActiveChart.SetSourceData Source:=Worksheets("Velocity Tracker").Range("C6:E" & iLastRow)
            ActiveChart.ChartTitle.Text = "Analytics Velocity Trend"
            ActiveChart.SeriesCollection(1).XValues = Worksheets("Velocity Tracker").Range("G6:G" & iLastRow)
    End Select
End Function

 

[Jon Peltier]

1. Are the sheet name ("Velocity Tracker") and chart name ("VelChart") in the code correct?

2. Why must you clear the chart? Is it because the number of rows in the data range changes? Then stop wasting time with VBA, and convert the data into a Table; don't include any extra rows. Make the chart, using data from entire columns of the Table. When data is added or removed from the Table, the Table changes size to accommodate, and formulas (including the chart's SERIES formulas) also change accordingly. see Easy Dynamic Charts Using Lists or Tables.