从图表中删除空系列（使用 VBA）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7875326/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Deleting empty Series out of Graph (with VBA)
提问by reggie
I am trying to remove all empty series out of an Excel graph.
我正在尝试从 Excel 图表中删除所有空系列。
Dim isEmptySeries As Boolean
For Series = 1 To .SeriesCollection.count
.SeriesCollection(Series).ApplyDataLabels Type:=xlDataLabelsShowValue, AutoText:=True, LegendKey:=False
isEmptySeries = True
For i = 1 To .SeriesCollection(Series).points.count
If .SeriesCollection(Series).points(i).DataLabel.Text = 0 Then
.SeriesCollection(Series).points(i).HasDataLabel = False
Else
isEmptySeries = False
.SeriesCollection(Series).points(i).DataLabel.Font.Size = 17
End If
Next i
If isEmptySeries Then
.SeriesCollection(Series).Delete
End If
Next Datenreihe
The script fails at the ApplyDatalabels line ("Method SeriesCollection of Object Chart failed"). I believe that Excel shifts the Series indexes when one of the Series is deleted? Is that the case? It's the only explanation that I have for the error.
脚本在 ApplyDatalabels 行失败（“Method SeriesCollection of Object Chart failed”）。我相信当系列之一被删除时，Excel 会移动系列索引吗？是这样吗？这是我对错误的唯一解释。
How else would I loop through the series and remove the ones that are empty?
否则我将如何遍历该系列并删除那些空的？
回答by chris neilsen
In these sorts of situations try looping in reverse order
在这些情况下尝试以相反的顺序循环
For i = .SeriesCollection(Series).points.count To 1 Step -1
That way the .Deletedoesn't effect the item yet to be looped through
这样.Delete就不会影响尚未循环的项目
