多图表创建系列重载

Question

在从我的大量数据中创建多个图表时，我遇到了一个问题。我需要创建4个独立的图表。在成功创建第一个图表之后，我为下一个图表编写的代码与此类似，但代码在下面的图表中生成了数量惊人的系列。我的第二张图共有6个系列，第三张图有33个系列，我的第四张图有48个系列。在我为每个图表选择的数据列中，B是我的日期，然后第二个范围是我的数据，但是在我的2-4个图表中，代码开始选择我没有指定的列。我该如何解决/修复这个问题？

创建这些图表的My Sub：

Sub ChartC()


Dim EmbChart As Chart, EmbChart1 As Chart, EmbChart2 As Chart, EmbChart3 As 
Chart
Dim cht As Object
Dim wks As Worksheet
Dim LastRow As Long

For Each wks In Worksheets

If wks.ChartObjects.Count > 0 Then
  wks.ChartObjects.Delete
End If
Next wks


With Application.ActiveSheet
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
End With


'Cement Inventory Graph
Set EmbChart = ActiveSheet.Shapes.AddChart(Left:=2760, Width:=1000, 
Top:=150, Height:=400).Chart


With EmbChart
    .SetSourceData Source:=Sheets("TestSheet_Const_Ktonnage").Range("B5:C" & LastRow)
    .Type = xlLine
    .HasTitle = True
    .ChartTitle.Text = "Year Overview of Total Cement Inventory"
    .SeriesCollection(1).Name = "Total Cement Inventory"
End With


'Clinker Inventory Graph
Set EmbChart1 = ActiveSheet.Shapes.AddChart(Left:=2760, Width:=1000, Top:=650, Height:=400).Chart


With EmbChart1
    .SetSourceData Source:=Range("B5:B" & LastRow, "H5:H" & LastRow)
    .Type = xlLine
    .HasTitle = True
    .ChartTitle.Text = "Year Overview of Clinker Inventory"
End With


'Operational Efficiency Kiln1
 Set EmbChart2 = ActiveSheet.Shapes.AddChart(Left:=2760, Width:=1000, Top:=1150, Height:=400).Chart


With EmbChart2
    .SetSourceData Source:=Range("B5:B" & LastRow, "AI5:AI" & LastRow)
    .Type = xlLine
    .HasTitle = True
    .ChartTitle.Text = "Year Overview of Operational Efficiency For Kiln1"
End With


'Operational Efficiency Kiln2
Set EmbChart3 = ActiveSheet.Shapes.AddChart(Left:=2760, Width:=1000, Top:=1650, Height:=400).Chart


With EmbChart3
    .SetSourceData Source:=Range("B5:B" & LastRow, "AX5:AX" & LastRow)
    .Type = xlLine
    .HasTitle = True
    .ChartTitle.Text = "Year Overview of Clinker Inventory"
End With

End Sub

Answer 1

您可以使用逗号分隔的范围对不相邻的列进行编程。因此，不是给Range两个参数，而是给它一个逗号分隔的范围参数：

Source:=Range("B5:B" & LastRow & "," & "AI5:AI" & LastRow)

所以如果LastRow是150，这个值就会减少到

Source:=Range("B5:B150,AI5:AI150")

希望这能有所帮助