vba - Excel vba Charting，编辑范围

Question

Sub StoragevsQuota()
    Charts.Add
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=Sheets("Data").Range("E1:G32")
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Storage Charts"
    ActiveChart.Parent.Name = "Used Space vs Disk Quota"
    ActiveChart.SetElement (msoElementChartTitleCenteredOverlay)
    ActiveChart.ChartTitle.Text = "Used Space vs Disk Quota"
End Sub

在 excel vba 中，单击 E1:G32 范围内的按钮即可为我生成图表.但这是一个静态范围

我想保持大致相同的语法，但它会循环遍历列 E，例如一直向下直到没有更多数据，所以它将是 32，然后将该范围扩展到 G32
它的列的长度可能因图表而异，我不会生成多少列，任何人都可以帮我设置它，以便像我上面所说的那样读取一列，直到没有数据输入然后在上面的案例中扩展范围3列？

谢谢Z

继续

Sub WeeklySuccessOrFailure() 
'On Error Resume Next
Dim lastRow As Long
With Sheets("Data")
lastRow = .range("AA" & Rows.Count).End(xlUp).Row
Charts.Add
ActiveChart.SetSourceData Source:=Sheets("Data").range("AA1:AA & lastRow, AD1:&      lastRow, AE1:AE & lastRow")
ActiveChart.ChartType = xlColumnClustered
ActiveChart.Location Where:=xlLocationAsObject, Name:="Job Charts"
ActiveChart.Parent.Name = "Total Weekly Success or Failure"
ActiveChart.SetElement (msoElementChartTitleCenteredOverlay)
ActiveChart.ChartTitle.Text = "Total Weekly Success Or Failure Of Jobs"
End With
End Sub

Answer 1

这是你正在尝试的吗？ ( 未测试 )

Sub StoragevsQuota()
    Dim lastRow As Long

    With Sheets("Data")
        lastRow = .Range("G" & Rows.Count).End(xlup).Row

        Charts.Add
        ActiveChart.ChartType = xlColumnClustered
        ActiveChart.SetSourceData Source:=.Range("E1:G" & lastRow)
        ActiveChart.Location Where:=xlLocationAsObject, Name:="Storage Charts"
        ActiveChart.Parent.Name = "Used Space vs Disk Quota"
        ActiveChart.SetElement (msoElementChartTitleCenteredOverlay)
        ActiveChart.ChartTitle.Text = "Used Space vs Disk Quota"
    End With
End Sub

跟进

@siddarth just to grab your attention any chance you could help me out with a problem I posted under comment below where boschiero commented, thanks – Zenaphor 8 secs ago

这是你正在尝试的吗？ ( 再次未经测试 )

ActiveChart.SetSourceData Source:=Sheets("Data").Range("AA1:AA" & lastRow & _
",AD1:AD" & lastRow & ",AE1:AE" & lastRow)