Dynamic Maximum Y Axis Scale using VBA Excel?

Question

I am not sure how to set the Y-Axis maximum in VBA for a bar chart created using the following data table:

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 Total. Also, the Total column may be changing in it's location as well, so I can't set it to the max of the Total column.

My current code for the bar chart is as follows:

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

Mrig Mrig · Accepted Answer · 2016-06-01T10:54:03

Write the following lines at the beginning of your code:

Range("A2").Select
Dim lastColumn As Long, lastRow As Long, max As Long

lastRow = Cells(Rows.Count, "A").End(xlUp).Row
lastColumn = ActiveSheet.Cells(2, Columns.Count).End(xlToLeft).Column
max = WorksheetFunction.max(Range(Cells(3, lastColumn), Cells(lastRow, lastColumn)))

This will give you max of totals, then you can write:

ActiveChart.Axes(xlValue).MaximumScale = max + 1

Dynamic Maximum Y Axis Scale using VBA Excel?

1 Answers