Deleting Charts in Excel

[KMillar]

Hope someone can help with what should be a simple one.
I'm trying to delete a variable number of charts from a sheet.

I create them through a macro that retrieves data and builds the charts. I then need to delete and re-create them when the user wants to re-run the query with different parameters. There could be none or there could be any number of charts. Just depends on what's been requested.

I've tried SkipVought's tip (09/01/2002):
For Each Chart In ActiveWorkbook.Charts
Charts.Delete
Next
but I need to declare the chart object I think. I've tried a number of other ways, but being fairly new to VBA, I think I'm missing some simple step.

Thanks.

 

[lambuhere1]

Millar I use the following method to delete my charts

Dim ch as chart

For each ch in activeworkbook.charts
ch.delete
Next ch

Hope this helps.

Ram P

 

[KMillar]

Thanks for the reply Ram.

I tried your suggestion, but no luck. It doesn't enter the For loop - it's like it doesn't recognise that there's charts on one of the sheets. There's 5 sheets in the workbook and there's currently 3 charts on the one I'm testing.

I found this command and tried it but it deleted the whole worksheet: ActiveWorkbook.Charts.Delete

Any other ideas?

 

[kevinclark]

Maybe try building on Ram's idea but use Shape ?


Sub Sample()
For Each Sht In ActiveWorkbook.Sheets
For Each cht In Worksheets(Sht.Name).Shapes
If cht.Type = msoChart Then
'MsgBox cht.Name
cht.delete
End If
Next cht
Next Sht
End Sub

 

[MikePR]

KMillar ... This simple code has always worked for me ....

Worksheets("YourWorksheetName&quot.ChartObjects.Delete

I embed this line in a typical user-prompt MsgBox structure if the user responds with "YES, I do wish to delete all charts."

To assure that the resultant worksheet is not totally bank I also programmatically put in a heading ....

Sheets("YourWorksheet Name&quot.Cells(1,2)= "This is a title"

Hope this helps.

 

[KMillar]

Thanks very much guys. Mission accomplished. Hopefully I can return the favour some time.