vba Excel vba图表，编辑范围

Excel vba Charting, editting the range

提问by Zenaphor

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

within excel vba this produces a graph for me at the click of a button from the range E1:G32. But that is a static range

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

I want to keep roughly the same syntax but for it to loop through column E for example all the way down until no more data so it would be 32 then extend that range to G32

我想保持大致相同的语法，但要让它循环遍历列 E，例如一直向下直到没有更多数据，所以它将是 32 然后将该范围扩展到 G32

its the length of the columns that can vary for graphs that I will produce not how many columns, could anyone help me set it up so that like i said above reads down a column until no data input then extend the range in the case above across 3 columns?

它的列的长度可能因图表而异，我不会生成多少列，谁能帮我设置它，以便像我上面说的那样读取一列，直到没有数据输入，然后扩展上述情况下的范围3列？

Thanks Z

谢谢Z

Continuation

延续

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

回答by Siddharth Rout

Is this what you are trying? (UNTESTED)

这是你正在尝试的吗？（未经测试）

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

FOLLOWUP

跟进

@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

@siddarth 只是为了吸引你的注意力，你可以帮助我解决我在下面的评论下发布的问题，boschiero 评论，谢谢 – Zenaphor 8 秒前

Is this what you are trying? (AGAIN UNTESTED)

这是你正在尝试的吗？（再次未经测试）

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

回答by j boschiero

Personally i prefer the start and end cells of my ranges to be dynamic, so my code would be something like:

我个人更喜欢我的范围的开始和结束单元格是动态的，所以我的代码会是这样的：

ActiveChart.SetSourceData Source:=.Range(.cells(1,5),.cells(7,lastRow))

But really only because i prefer the cell notation for specifying ranges. Siddharth's answer is pretty much spot on.

但实际上只是因为我更喜欢用于指定范围的单元格符号。悉达多的回答非常到位。

For multiple, non-contiguous ranges, i would create a Range variable and then use Union to assign whatever ranges i want to it.

对于多个非连续范围，我会创建一个 Range 变量，然后使用 Union 分配我想要的任何范围。

So let's say i wanted columns AA, AD and AE, as in Siddharth's example, i would do the following:

因此，假设我想要列 AA、AD 和 AE，就像在 Siddharth 的示例中一样，我将执行以下操作：

Sub WeeklySuccessOrFailure() 
'On Error Resume Next
Dim lastRow As Long
Dim myRange as Range
With Sheets("Data")
lastRow = .range("AA" & Rows.Count).End(xlUp).Row
Charts.Add
Set myRange = Union(.range(.cells(1,27),cells(lastRow,27)),_
.range(.cells(1,30),.cells(lastRow,30)),_
.range(.cells(1,31),.cells(lastRow,31)))
ActiveChart.SetSourceData Source:= myRange
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

I did not test the above code but i have used the myRange and Union method before.

我没有测试上面的代码，但我之前使用过 myRange 和 Union 方法。

I would say that it is somewhat clunkier than Siddharth's suggestion, but i find it easier to use for automating purposes. If you aren't ever going to automate the part of your macro that finds the columns to graph, i.e. it's always going to be the same columns, then Siddharth's method is easier and probably more intuitive.

我会说它比 Siddharth 的建议更笨拙，但我发现它更容易用于自动化目的。如果您永远不会自动执行宏中查找要绘制的列的部分，即它总是相同的列，那么 Siddharth 的方法更简单，也可能更直观。

回答by Jon Peltier

If the data is a nice rectangle, surrounded by blank rows and columns, you can make it very easy. Select a cell in the data range, then run the code, which finds the entire region of data containing the active cell:

如果数据是一个漂亮的矩形，周围是空白的行和列，你可以很容易地做到这一点。在数据范围内选择一个单元格，然后运行代码，它会找到包含活动单元格的整个数据区域：

Sub MakeChartFromIndicatedRange1()
  Dim rng As Range
  Set rng = ActiveCell.CurrentRegion
  Charts.Add
  ActiveChart.SetSourceData Source:=rng
  ' etc.
End Sub

Or if the user is going to select the entire range, this code uses the selected region:

或者，如果用户要选择整个范围，则此代码使用所选区域：

Sub MakeChartFromIndicatedRange2()
  Dim rng As Range
  Set rng = Selection
  Charts.Add
  ActiveChart.SetSourceData Source:=rng
  ' etc.
End Sub

Or totally mimic Excel's built-in range definer, which uses the selected range if it's more than one cell, or the whole region the active cell is contained in if only one cell is selected.:

或者完全模仿 Excel 的内置范围定义器，如果它有多个单元格，则使用选定的范围，或者如果只选择一个单元格，则使用活动单元格所在的整个区域。：

Sub MakeChartFromIndicatedRange()
  Dim rng As Range
  If Selection.Cells.Count = 1 Then
    Set rng = ActiveCell.CurrentRegion
  Else
    Set rng = Selection
  End If
  Charts.Add
  ActiveChart.SetSourceData Source:=rng
  ' etc.
End Sub