Excel vba图表，编辑范围 [英] Excel vba Charting, editting the range

问题描述

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 范围内的按钮为我生成图表。但是这是一个静态范围

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

我想保留大致相同的语法，但是为了循环遍历列E，例如一直下降，直到没有更多的数据，所以它会然后将该范围扩展到 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

它的长度可以为我将要产生不了多少列，任何人都可以帮助我设置它，就像我上面说的那样读取一列，直到没有数据输入，然后扩大上述范围在3列？

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?

谢谢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

推荐答案

这是你正在尝试的吗？ （ UNTESTED ）

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只是抓住你的注意力任何机会，你可以帮助我出现一个问题，我发布在下面的评论下面boschiero评论，谢谢 - Zenaphor 8 secs ago

@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

这是你正在尝试的吗？ （ AGAIN UNTESTED ）

Is this what you are trying? (AGAIN UNTESTED)

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

这篇关于Excel vba图表，编辑范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！