Cannot delete a chart series using VBA in Excel 2010

3
votes

I have a VBA macro written in Excel 2003 which has worked perfectly well in Excel 2003 and 2007. However, it falls over in Excel 2010 and I cannot tell why.

The macro updates the series in a chart after the user has added or removed data. The first step (after data validation) is to delete all but the first series in the chart.

'clear all chart series except for series 1
Do While theChart.SeriesCollection.Count > 1
    theChart.SeriesCollection(2).Delete
Loop

Execution halts in the very first iteration of the loop, with the error dialog "Method 'delete' of object 'series' failed."

I thought perhaps the object model had changed in Excel 2010, so I used the macro recorder to record the action of deleting a series:

ActiveSheet.ChartObjects("Plant Feed").Activate
ActiveChart.SeriesCollection(3).Select
Selection.Delete

Running the recorded macro (with Series 3 re-instated obviously) stops on the second line with "Method 'select' of object 'series' failed."

I then added an object variable and some msgbox lines to try to debug the problem:

Dim theSeries As Series
ActiveSheet.ChartObjects("Plant Feed").Activate
MsgBox (ActiveChart.SeriesCollection(3).Name)
Set theSeries = ActiveChart.SeriesCollection(3)
MsgBox (theSeries.Name)
theSeries.Delete

The object variable sets correctly and the message boxes give the right output, but it still falls over on theSeries.delete.

Finally, I repeated all of the above using a brand new chart created in Excel 2010, just in case it was a legacy problem from 2003, but I got the same error.

I am tearing my hair out. I've searched on line for solutions to no avail, which is what led me to this Stack Overflow site. I would appreciate any help that anyone can offer.

Regards,

Darren

2
excelvba
+1 for detailing the steps you've taken so far. Question: Any chance the sheet is protected? – Rachel Hettinger
Thank you Rachel. I wish it was that, even though I would feel a bit silly. Since I posted, I have determined one more thing, which makes me think that it is some sort of 2003 legacy. I don't get the problem if I create a new chart in a new workbook. But new charts in the existing workbook don't work, even if I delete all other charts and VBA modules first. I think I'm going to have to recreate the whole workbook from scratch. <sigh> – Darren
Rachel's +1 didn't work but you desserve it. By the way, you can try to set a breakpoint on the line that raises an error and have a look at theChart.SeriesCollection(2) object and check what it is – JMax

2 Answers

0
votes

Have you tried it this way instead?

For x = theChart.SeriesCollection.Count To 2 Step -1
   theChart.SeriesCollection(x).Delete
Next x
0
votes

Delete items from the beginning of the collection, there will always be a first element, until you deleted them all.

Do While theChart.SeriesCollection.Count > 1
     theChart.SeriesCollection(1).Delete   
     'theChart.SeriesCollection(2).Delete    
Loop