title: VBA Chart: Ranges until last non-empty row as source data
body: <p>So I have a spreadsheet with data spanning A2 to AF17, I hid the majority of columns such that now only columns: I, AB, AD, AF are showing, where column I is the range I want as axis labels range, and column AF as the value range.</p>
  
  <p>I coded the following to create a graph that dynamically uses range until last non-empty row as Values:</p>
  
  <pre><code>Private Sub CommandButton2_Click()
  
  Dim MyEmbeddedChart As Chart
  
  Set MyEmbeddedChart = Sheets("Analysis").Shapes.AddChart(Width:=634, Height:=250).Chart
  
  With MyEmbeddedChart
      .ChartType = xlBarClustered
      .ChartStyle = 339
      .SetSourceData Source:=Sheets("Analysis").Range("AF2", Cells(Rows.Count,  1).End(xlUp))
      .Legend.Delete
      .Axes(xlValue).Delete
      .Axes(xlValue).MajorGridlines.Format.Line.Visible = msoFalse
      .ApplyDataLabels
  End With
  
  End Sub
  </code></pre>
  
  <p>Now even though the resulting graph automatically shows column I as axis labels range, it is only because column I is the leftmost visible column right now. That is, in actuality the source data range is A2:AF17, and the axis labels range is actually A2:AD17. And as soon as I unhide the columns, naturally the axis labels got messed up.</p>
  
  <p>Even though the current graph serves my purpose, it is definitely not ideal. So how do I set the source data to be precisely column I as the axis label range and column AF as the value range while keep using the last non-empty row method?</p>
  
  <p>I am still relatively new to VBA, any help is appreciated.</p>