Deleting an Empty Series in a Chart

[MDB]

Hello All,

I’m trying to re-use a Chart Object to dynamically display differen
sets of data, using VBA.

To clear the chart of one data set, before generating the next, I’
deleting each data series by looping through the SeriesCollectio
object, ie:

For i = ActiveChart.SeriesCollection.Count To 1 Step -1
ActiveChart.SeriesCollection(i).Delete
Next i

However, I’m encountering a problem when a series is empty (ie
contains all #NA values). When the delete method is called, I ge
“Run-time error ‘1004’: Delete method of Series class failed.”

Does anyone have a suggestion for deleting an empty series, or anothe
way of clearing out a chart object?

I’ve considered generating a new chart each time, but gave up on th
complexity of positioning and sizing the new chart.

Some sample data would look like:

Series
#1 #2 #3
-- -- ---
1 2 #NA
2 4 #NA
3 6 #NA
4 8 #NA
5 10 #NA

Deleting series #3 fails.

Thank

 

[Andy Pope]

Hi,

I did a quick test and could only get the error if the chart type was
either line or xy scatter. Column, bars and areas all quite happily
delete the series.
So maybe you could change the chart type of each series prior to
deletion. Or if you are deleting ALL the series then switch the whole
chart type whilst deleting.

For i = ActiveChart.SeriesCollection.Count To 1 Step -1
ActiveChart.SeriesCollection(i).ChartType = xlColumnClustered
ActiveChart.SeriesCollection(i).Delete
Next i

Cheers
Andy

 

[MDB]

Thanks Andy.

That worked perfectly.

I don't know if I would have ever figured out that the chart type make
a difference.

Sincerely,
MD

 

[MDB]

Following up...

As often happens, after learning about all the internal control we hav
with VBA, I figured out the simplest way to accomplish my goal o
clearing chart data is the following ONE line:

ActiveChart.ChartArea.ClearContents

: