使用 VBA 从图表中删除系列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41463504/
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
Remove series from chart with VBA
提问by M Kaye
I am creating a chart in excel via vba code. I am using contiguous data and the chart pops up no problem, however there is an extra series named "Series 3" that I didn't ask for and need to get rid of (via deleting it or omitting in the first place). It has no data in it but needs to be removed from the legend at least. Here is my code :
我正在通过 vba 代码在 excel 中创建图表。我正在使用连续数据并且图表弹出没有问题，但是还有一个名为“Series 3”的额外系列，我没有要求并且需要摆脱（通过删除它或首先省略）。它没有数据，但至少需要从图例中删除。这是我的代码：
Dim MyChtObj As ChartObject
Dim Sht1 As Worksheet
Dim ShtName As String
Set Sht1 = Worksheets("Parameter Forecasts")
ShtName = Sht1.Name
Set MyChtObj = Sht1.ChartObjects.Add(100, 100, 500, 500)
Set a = Sht1.Range("E37", Sht1.Range("E37").End(xlToRight))
Set b = Sht1.Range("E38", Sht1.Range("E38").End(xlToRight))
Set InputData = Union(a, b)
With MyChtObj.Chart
.ChartType = xlLineMarkers
.SetSourceData InputData
.PlotBy = xlRows
.SeriesCollection.NewSeries.XValues = Sht1.Range("F36", Sht1.Range("F36").End(xlToRight))
End With
I have already tried:
我已经尝试过：
MyChtObj.SeriesCollection(3).Delete
MyChtObj.SeriesCollection(3).Delete
But this does not work.
但这不起作用。
Thanks in advance, Max
提前致谢，马克斯
回答by Shai Rado
The SeriesCollectionis part of the ChartObject.Chartobject, and not the ChartObject.
该SeriesCollection是金ChartObject.Chart的对象，而不是ChartObject。
Therfore, replace your line of:
因此，请替换您的以下行：
MyChtObj.SeriesCollection(3).Delete
With:
和：
MyChtObj.Chart.SeriesCollection(3).Delete
回答by Jon Peltier
Your line of code
你的代码行
.SeriesCollection.NewSeries.XValues = Sht1.Range("F36", Sht1.Range("F36").End(xlToRight))
is adding a third series via .NewSeries. Change it to
正在通过.NewSeries.添加第三个系列。将其更改为
.SeriesCollection(1).XValues = Sht1.Range("F36", Sht1.Range("F36").End(xlToRight))
Now there's nothing to delete.
现在没什么可删除的了。
回答by sabrinahewa
THis doesn't seem to work always
这似乎并不总是有效
.SeriesCollection(1).XValues = Sht1.Range("F36", Sht1.Range("F36").End(xlToRight))
This wont work if there is no series 3 to delete (some times randomly this series 3 doesn't appear by default then you'll get an error for this line )
如果没有要删除的系列 3，这将不起作用（有时，默认情况下不会随机出现系列 3，那么您将收到此行的错误）
MyChtObj.Chart.SeriesCollection(3).Delete
Here's a fast n dirty trick. Add this (repeated 3 times here but repeat more depending on your sheet because different sheets depending on the nr of cells populated, generate different numbers of random series). It will keep deleting the series that falls into the 3rd position in the "list" with every deletion until nr or series is 2
这是一个快速的肮脏的把戏。添加这个（在这里重复 3 次，但根据您的工作表重复更多，因为不同的工作表取决于填充的单元格的 nr，生成不同数量的随机系列）。每次删除时，它将不断删除落入“列表”中第 3 个位置的系列，直到 nr 或系列为 2
If .SeriesCollection.Count > 2 Then
MyChtObj.Chart.SeriesCollection(3).Delete
End If
If .SeriesCollection.Count > 2 Then
MyChtObj.Chart.SeriesCollection(3).Delete
End If
If .SeriesCollection.Count > 2 Then
MyChtObj.Chart.SeriesCollection(3).Delete
End If
