从图中删除空系列（带VBA） [英] Deleting empty Series out of Graph (with 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屋！