Automatically scale and format value axis based on max value

[excel_help1234]

Hi,

I am trying to create VBA code that formats the y-axis labels of 10+ charts as ##B , ##M , ##k depending on the size of the data being plotted. Is there a way to set a VBA variable equal to the height (max value or sum of values) of the "tallest" data point, whether it is the highest value in a line chart or the tallest set of columns in a stacked column chart? Excel has to use something for the automatic scaling of these charts, is there any way for me to reference whatever number excel uses?

Sorry if that sounds confusing, feel free to reach out for any clarifications or questions.

Thanks!

 

[Jon Peltier]

If you want the maximum and minimum of the vertical axis in the active line, column, or xy scatter chart:

maximum: ActiveChart.Axes(xlValue, xlPrimary).MaximumScale
minimum: ActiveChart.Axes(xlValue, xlPrimary).MinimumScale

I have two tutorials that show how to calculate your own axis scales:

Calculate Nice Axis Scales in Your Excel Worksheet
Calculate Nice Axis Scales in Excel VBA