Often the built-in Excel fitting of chart axis minimum and maximum provides a larger range than required for the underlying data. Options exist to calculate the minimum and maximum data values in cells for subsequent use on chart axis parameters. This VBA function analyzes the data for all data series in a given chart and then trims the minimum and maximum axis points of the vertical axis based on the major unit amount defined for the chart.
VBA Code:
Sub Trim_Vertical_Axis(chtname As String)
'chtname is name of the chart in the active sheet
Dim sernum As Long
Dim chtdata As Variant
Dim munit As Double
Dim vmin As Double
Dim vmax As Double
Dim nmin As Double
Dim nmax As Double
Dim i As Long
Dim snum As Long
ActiveSheet.ChartObjects(chtname).Activate
For snum = 1 To ActiveChart.SeriesCollection.Count
chtdata = ActiveChart.SeriesCollection(snum).Values
For i = 1 To UBound(chtdata)
If i = 1 And snum = 1 Then
vmax = chtdata(i)
vmin = chtdata(i)
End If
If chtdata(i) > vmax Then
vmax = chtdata(i)
End If
If chtdata(i) < vmin Then
vmin = chtdata(i)
End If
Next i
Next snum
munit = ActiveChart.Axes(xlValue).MajorUnit
nmax = Round(vmax / munit) * munit
If nmax < vmax Then
nmax = nmax + munit
End If
nmin = Round(vmin / munit) * munit
If nmin > vmin Then
nmin = nmin - munit
End If
