Set Chart/Graph Minimum/Maximum Scale To Nth Smallest & Largest Value

  • Hi,


    I am trying to manually format a few graphs in my sheet. I, however, leave the major units of the axes to be automatically calculated. The code that I use is given below:


    Code
    ActiveChart.Axes(xlValue).Select
    With ActiveChart.Axes(xlValue)
        .MinimumScale = Range("Min").Value
        .MaximumScale = Range("Max").Value
    End With


    Now what this does is that it sets the maximum of the scale exactly equal to maximum of the range being plotted and hence a few parts of my graph overlap with the border of the plot area.


    Is there any way in which I can round the max and min scale of y-axis to the next highest and lowest major unit respectively or may be add and subtract a value proportional to the major unit of the y-axis


    I cannot add a constant in the code given above since I that does not suite my requirements.


    Thanks a lot.

  • Re: Set Chart/Graph Minimum/Maximum Scale To Nth Smallest & Largest Value


    Possible


    [vba]
    With ActiveChart.Axes(xlValue)
    .MinimumScale = _
    Application.WorksheetFunction.Floor(range("Min"),activechart.Axes(xlvalue).majorunit)
    .MaximumScale = _
    Application.WorksheetFunction.Ceiling(range("Max"),activechart.Axes(xlvalue).majorunit)
    End With
    [/vba]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!