title: Dynamic Maximum Y Axis Scale using VBA Excel?
body: <p>I am not sure how to set the Y-Axis maximum in VBA for a bar chart created using the following data table:
  <a href="https://i.stack.imgur.com/ztCnG.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ztCnG.jpg" alt="enter image description here"></a></p>
  
  <p>The data table will be dynamically changing (more Features or fewer features), so I would like the Y-Axis maximum to be set to a value that is 1 greater than the max of the <code>Total</code>.  Also, the <code>Total</code> column may be changing in it's location as well, so I can't set it to the max of the <code>Total</code> column.</p>
  
  <p>My current code for the bar chart is as follows:</p>
  
  <pre><code>Sub Create_BarChart()
  
      Range("A2").Select
      Dim lastColumn As Long
      lastColumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
  
      Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, ActiveCell.End(xlToRight).Column)).Select
      ActiveSheet.Shapes.AddChart.Select
      ActiveChart.ChartType = xlColumnStacked
      ActiveChart.PlotBy = xlColumns
      ActiveChart.SetElement (msoElementDataLabelCenter)
  ' Format the data labels of the Total
      ActiveChart.SeriesCollection("Total").Format.Fill.Visible = msoFalse
      ActiveChart.SeriesCollection("Total").DataLabels.Font.Bold = True
      ActiveChart.SeriesCollection("Total").DataLabels.Select
      ActiveChart.Legend.LegendEntries(1).Font.Bold = True
      Selection.Position = xlLabelPositionInsideBase
  
  ' Set the maximum and minimum values of the y-axis
      ' ActiveChart.Axes(xlValue).MaximumScale = Application.WorksheetFunction.(Columns(lastColumn))
      ActiveChart.Axes(xlValue).MinimumScale = 0
      ActiveChart.Axes(xlValue).MaximumScale = ?????????????????
  
  ' Change the size of the Chart
      Dim Chart As ChartObject
  
  For Each Chart In ActiveSheet.ChartObjects
  
  With Chart.Parent
  
  ' change the numbers in the below brackets (5) to change the size of the chart. Here we are using inches to set the chart size.
  Chart.Height = Application.InchesToPoints(2.5)
  Chart.Width = Application.InchesToPoints(5)
  
  End With
  
  Next
  
  
  End Sub
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/u3P75.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/u3P75.jpg" alt="enter image description here"></a></p>