Excel 2010 Macro Scale the Sparklines
The default choice for the sparkline vertical axis is that each sparkline will have a different minimum and maximum. Two other choices are available. One choice is to group all the sparklines together and continue to allow Excel to choose the minimum and maximum scale. You still won’t know the exact values chosen for the minimum and maximum.
To force the sparklines to have the same automatic scale:
‘ Allow automatic axis scale, but all three of them the same
With SG.Axes.Vertical
.MinScaleType = xlSparkScaleGroup
.MaxScaleType = xlSparkScaleGroup
End With
Note that the .Axes belongs to the sparkline group, not to the individual sparklines themselves. Almost all of the good properties are applied at the SparklineGroup level. If you wanted one sparkline to have automatic scale and another sparkline to have a fixed scale, you have to create each of those sparklines separately, or ungroup them.
The second choice is to take complete control and assign a minimum and maximum for the vertical axis scale.
The following code forces the sparklines to run from a minimum of 0 up to a maximum that rounds up to the next 100 above the largest value:
Set AF = Application.WorksheetFunction
AllMax = AF.Max(WSD.Range(“D2:F” & FinalRow))
AllMax = Int(AllMax / 100) * 100 + 100
‘ Allow automatic axis scale, but all three of them the same
With SG.Axes.Vertical
.MinScaleType = xlSparkScaleCustom
.MaxScaleType = xlSparkScaleCustom
.CustomMinScaleValue = 0
.CustomMaxScaleValue = AllMax
End With
To place the minimum scale on the lower left and the upper scale on the upper right, use below code:
‘ Add two labels to show minimum and maximum
With WSL.Range(“A2”)
.Value = AllMin
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.Font.Size = 8
.Font.Bold = True
.WrapText = True
End With
With WSL.Range(“E2”)
.Value = AllMax
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.Font.Size = 8
.Font.Bold = True
End With