Delete multiple chartobjects from one chart sheet

[userkjay]

The objective: Use VBA to delete multiple chartobjects from one chart sheet.

If multiple chartobjects are created on one chart using the following code:

[FONT=Arial]Sub ChartCreate()[/FONT]
[FONT=Arial]Dim chtTemp  As ChartObject[/FONT]
[FONT=Arial]Charts.Add.Location Where:=xlLocationAsNewSheet, Name:="My Chart"[/FONT]
[FONT=Arial]For j = 1 To 3[/FONT]
[FONT=Arial]   Set chtTemp = Charts("My Chart").ChartObjects.Add(4 ^ j, 4 ^ j, 10, 10)[/FONT]
[FONT=Arial]Next[/FONT]
[FONT=Arial]MsgBox Charts("My Chart").ChartObjects.Count[/FONT]
[FONT=Arial]End Sub[/FONT]

The 3 chartobjects on the chart “My Chart” can be selected with the mouse and deleted by hitting the delete key. I can not duplicate this using VBA. I have tried the macro recorder, but it does not capture the delete command. I have tried using sendkeys, with no success. One of the many methods of VBA code that I have tried is below:

[FONT=Arial]Sub ChartDelete()[/FONT]
[FONT=Arial]For i = 1 To ActiveChart.ChartObjects.Count[/FONT]
[FONT=Arial]   ActiveChart.ChartObjects(ActiveChart.ChartObjects(i).Name).Delete[/FONT]
[FONT=Arial]Next[/FONT]
[FONT=Arial]End Sub[/FONT]

If anyone knows of a work around it would be greatly appreciated. I have found this problem to be a challenge.

 

[shg]

Let me try again:

Sub ChartDelete()
    Dim i As Long
    
    Do While ActiveChart.ChartObjects.Count
        ActiveChart.ChartObjects(1).Delete
    Loop
End Sub

 

[userkjay]

I assure you the loop executes. To prove it executes run the following. You will be promoted with the 3 chart names. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

Sub ChartDelete()
For i = 1 To ActiveChart.ChartObjects.Count
    MsgBox ActiveChart.ChartObjects(i).Name
    ActiveChart.ChartObjects(ActiveChart.ChartObjects(i).Name).Delete
Next
End Sub

 

[userkjay]

Let me try again:

Sub ChartDelete()
    Dim i As Long
 
    Do While ActiveChart.ChartObjects.Count
        ActiveChart.ChartObjects(1).Delete
    Loop
End Sub

I'm sorry, but this suggestion does not work.

 

[shg]

Yep. Now I'll actually test something ...

 

[userkjay]

Yep. Now I'll actually test something ...

I look forward to what you discover. I have been stumped by this for a couple of days.

 

[shg]

Curious. This works in Excel 2003 on both worksheets and chart sheets, and in Excel 2007 on worksheets, but not on 2007 chart sheets:

Sub AllChartsDelete()
    Dim chObj       As ChartObject
 
    For Each chObj In ActiveSheet.ChartObjects
        chObj.Delete
    Next chObj
End Sub

I'll see If I can find someone smarter ...

 

[userkjay]

Curious. This works in Excel 2003 on both worksheets and chart sheets, and in Excel 2007 on worksheets, but not on 2007 chart sheets.

Perhaps it is a bug in the later versions of Excel. I should state that I am using Excel 2010.

 

[pgc01]

Hi

Try also:

    Charts("Chart1").ChartObjects.Delete

or

    ActiveSheet.ChartObjects.Delete

 

[userkjay]

Hi

Try also:

    Charts("Chart1").ChartObjects.Delete

or

    ActiveSheet.ChartObjects.Delete

pgc01, both of the above methods produce an error.