title: Excel vba Charting, editting the range
body: <pre><code>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
  </code></pre>
  
  <p>within excel vba this produces a graph for me at the click of a button from the range <code>E1:G32</code>. But that is a static range</p>
  
  <p>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 <code>G32</code></p>
  
  <p>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?</p>
  
  <p>Thanks Z</p>
  
  <p>Continuation</p>
  
  <pre><code>Sub WeeklySuccessOrFailure() 
  'On Error Resume Next
  Dim lastRow As Long
  With Sheets("Data")
  lastRow = .range("AA" &amp; Rows.Count).End(xlUp).Row
  Charts.Add
  ActiveChart.SetSourceData Source:=Sheets("Data").range("AA1:AA &amp; lastRow, AD1:&amp;      lastRow, AE1:AE &amp; 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
  </code></pre>