Removing Charts in Excel

Question

I was trying to chart some RTD data and accidentally ended up with hundreds of charts on the same worksheet. Now I'm trying to undo my error and remove all of the charts but I'm not having much luck with it.

I recorded a macro where I deleted one of the charts manually and then tried editing the code to loop through all of the charts but I keep getting an error. My code is below:

Sub Macro3()

Dim i As Integer

For i = 1 To 100
  Sheets("Calculations").Select
  ActiveSheet.ChartObjects("Chart " & CStr(i)).Activate
  ActiveChart.ChartArea.Select
  ActiveWindow.Visible = False
  Selection.Delete
Next i
End Sub

When I try running this, I get an error saying that the ChartObjects property was inaccessible from the Worksheet class.

I'm sure there's a simple explanation/solution to this but I've learned that VBA sometimes does things a little differently than you might be expecting. So, I guess my question is, how do I remove the charts without having to go through each one at a time?

Any help would be appreciated. Thanks.

Answer 1

It's quite possible you are attempting to access a chart by name which no longer exists. Try accessing the charts by index using ChartObjects(i) instead.

Answer 2

Try this, it will get rid of all charts on your sheet regardless of their names.

Sub Macro3()
    Worksheets("Calculations").ChartObjects.Delete
End Sub