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

 

[NdNoviceHlp]

Maybe something like this...

Dim S As Series
For Each S In Sheets("Velocity Tracker").ChartObjects("VelChart").Chart.SeriesCollection
      S.Delete
Next S

HTH. Dave

 

[jammerules]

Thanks for the reply, Dave. Sadly, it didn't do anything. However, if I put a breakpoint at the beginning of the 'For' and loop thru, then it is clearing the chart. What gives?

 

[NdNoviceHlp]

If you place the code in the function it should work. I suspect that you need to activate the chart before running the code by itself. Trial...

Sheets("Velocity Tracker").ChartObjects("VelChart").Activate

before running the code. HTH. Dave

 

[jammerules]

Still no dice. See my code below:

 

[jammerules]

I created a function and called it from the sub-routine. Even then it wouldn't clear upon calling the function.

 

[NdNoviceHlp]

Well I'm not quite sure. I use that code without problems. Maybe trial selecting the sheet, then activating the chart and then removing the series. So...

Sheets("Velocity Tracker").Select
Sheets("Velocity Tracker").ChartObjects("VelChart").Activate
For Each S In Sheets("Velocity Tracker").ChartObjects("VelChart").Chart.SeriesCollection
      S.Delete
Next S

Dave

 

[jammerules]

Still won't work. Thanks for trying to help.

 

[NdNoviceHlp]

Well, that is puzzling. Perhaps Jon or others will have time to offer further assistance. Sorry I wasn't able to help. Dave

 

[jammerules]

No worries, I appreciate your help nevertheless, Dave. Thank you again.