Error deleting embedded charts from a worksheet when there are no charts

[saltire1963]

I have a button in a worksheet that when clicked takes data from that same sheet and creates 3 dynamic charts embedded in same sheet. I want to get rid of the old charts the next time I click the button so that I don't build over the top of the old charts with the new ones. I start my sub with ChartObjects.Delete and any existing charts are deleted as expected, however if I run my code again when there a no charts in the sheet I get a Run-time error. '1004' This is the same as would happen if someone deleted the charts in the worksheet and then clicked the button. Is there a way I can avoid this error? Thanks in advance

 

[Domenic]

Try the following instead...

    Dim chrtObj As ChartObject
    
    For Each chrtObj In ThisWorkbook.Worksheets("Sheet1").ChartObjects 'change the sheet name accordingly
        chrtObj.Delete
    Next chrtObj

Hope this helps!

 

[NdNoviceHlp]

Not sure why you want to delete a chart when you're not done with it? You can change the series and chart formatting rather than re-creating new charts. In the end, I found to get rid of charts, this worked after many attempts had failed. HTH. Dave

Sheets("Sheet1").Select
Sheets("Sheet1").ChartObjects("ChartName").Activate
Sheets("Sheet1").ChartObjects("ChartName").Chart.ChartArea.Select
ActiveWindow.Visible = False
Sheets("Sheet1").ChartObjects("ChartName").Chart.Parent.Delete

ps. adjust the sheet name and "ChartName" to suit

 

[saltire1963]

Try the following instead...

    Dim chrtObj As ChartObject
   
    For Each chrtObj In ThisWorkbook.Worksheets("Sheet1").ChartObjects 'change the sheet name accordingly
        chrtObj.Delete
    Next chrtObj

Hope this helps!

Hi Domenic, when I put this code in I now get Run-time error '9' Subscript out of range

 

[saltire1963]

Not sure why you want to delete a chart when you're not done with it? You can change the series and chart formatting rather than re-creating new charts. In the end, I found to get rid of charts, this worked after many attempts had failed. HTH. Dave

Sheets("Sheet1").Select
Sheets("Sheet1").ChartObjects("ChartName").Activate
Sheets("Sheet1").ChartObjects("ChartName").Chart.ChartArea.Select
ActiveWindow.Visible = False
Sheets("Sheet1").ChartObjects("ChartName").Chart.Parent.Delete

ps. adjust the sheet name and "ChartName" to suit

Hi, when I try this I get Run-time error '5' Invalid procedure call or argument after the second line

Sheets("League").Select
Sheets("League").ChartObjects("ChBestScore").Activate
Sheets("League").ChartObjects("ChBestScore").Chart.ChartArea.Select
ActiveWindow.Visible = False
Sheets("League").ChartObjects("ChBestScore").Chart.Parent.Delete

 

[Domenic]

You'll need to change the sheet name, where I specified in the code.

 

[NdNoviceHlp]

Hmmm... I just created a chart and adjusted the sheet name and chart name and ran the code I posted.... deleted the chart. Not sure why the code errors for you? Dave