# Scaling macro doesn't work well for percentage chart

#### artz

##### Well-known Member
Hi,

I am using a scaling macro to minimize white space on a line chart. For whole numbers, it does a beautiful job of scaling. On my other chart, where the data is fractional and is plotted with percent on the Y axis, it does terribly at minimizing white space. Below is the code that I am using:
Code:
``````Sub ScaleCharts()
'
' ScaleCharts Macro
'
Dim objCht As ChartObject
Dim maxi As Double, mini As Double, Range, Adj As Double, xMax As Double, xMin As Double
Dim Round As Integer, Order As Integer, x As Integer, i As Integer

Application.ScreenUpdating = False
For x = 1 To ActiveWorkbook.Sheets.Count
Application.StatusBar = "Crunching sheet " & x & " of " & ActiveWorkbook.Sheets.Count

For Each objCht In Sheets(x).ChartObjects
If objCht.Chart.ChartType = xlLine Or objCht.Chart.ChartType = xlXYScatter Then
With objCht.Chart
For i = 0 To .SeriesCollection.Count - 1 'Loop through all the series in the chart

'Get the Max and Min values of the data in the chart
maxi = Application.Max(.SeriesCollection(i + 1).Values)
mini = Application.Min(.SeriesCollection(i + 1).Values)
Range = maxi - mini

If Range > 1 Then
Order = Len(Int(Range))
Adj = 10 ^ (Order - 2)
Round = -1 * (Order - 1)
ElseIf Range <> 0 Then
Order = Len(Int(1 / Range))
Adj = 10 ^ (-1 * Order)
Round = Order - 1
End If

'Get the Max and Min values for the axis based on the data
If i = 0 Or WorksheetFunction.Round(maxi, Round + 1) + Adj > xMax Then
xMax = WorksheetFunction.Round(maxi, Round + 1) + Adj
End If

If i = 0 Or WorksheetFunction.Round(mini, Round + 1) - Adj < xMin Then
xMin = WorksheetFunction.Round(mini, Round + 1) - Adj
End If
Next i

With .Axes(xlValue)
.MaximumScale = xMax
.MinimumScale = xMin
End With
End With
End If
Next objCht
Next x
Application.ScreenUpdating = True
Application.StatusBar = False

End Sub``````

Does anybody have a suggestion to optimize this scaling code for fractional data?

Thanks,

Art

### Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Replies
5
Views
603
Replies
1
Views
395
Replies
3
Views
2K
Replies
0
Views
480
Replies
0
Views
142

1,214,769
Messages
6,121,434
Members
449,032
Latest member
egspen2

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back