从图中删除空系列(带VBA) [英] Deleting empty Series out of Graph (with VBA)
本文介绍了从图中删除空系列(带VBA)的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
Dim isEmptySeries As Boolean
For Series = 1 To .SeriesCollection.count
.SeriesCollection(Series).ApplyDataLabels Type:= xlDataLabelsShowValue,AutoText:= True,LegendKey:= False
isEmptySeries = True
对于i = 1 To .SeriesCollection(Series).points.count
如果.SeriesCollection(Series).points(i).DataLabel.Text = 0然后
.SeriesCollection(Series).points(i).HasDataLabel = False
Else
isEmptySeries = False
.SeriesCollection(Series).points(i).DataLabel.Font.Size = 17
End If
Next i
如果isEmptySeries然后
.SeriesCollection(系列).Delete
结束If
下一页Datenreihe
脚本在ApplyDatalabels行失败(Method SeriesCollection of Objec t图失败)。
我相信当系列中的一个被删除时,Excel会移动系列索引?是这样吗?这是我错误的唯一解释。
我还将如何循环浏览系列,并删除那些是空的?
解决方案
在这种情况下,尝试以相反顺序循环
For i = .SeriesCollection(Series).points.count到1 Step -1
.Delete
不会影响尚未循环的项目
I am trying to remove all empty series out of an Excel graph.
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.
How else would I loop through the series and remove the ones that are empty?
解决方案
In these sorts of situations try looping in reverse order
For i = .SeriesCollection(Series).points.count To 1 Step -1
That way the .Delete
doesn't effect the item yet to be looped through
这篇关于从图中删除空系列(带VBA)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文