I am currently working on a way to change the scales on charts. I have several tables on a sheet with corresponding charts, and I would like the chart axes scales to be selected automatically. However, when I select 'auto' in the format axis menu, it uses illogical numbers, displaying large empty areas above and below the highest and lowest points. I would like the lowest and highest point in the table to be the lowest and highest point in the chart.

To get the lowest and highest number of each table, I have used the following formulas:

=ROUNDDOWN(MIN($C$21:$E$31)*1,01;1)

=ROUNDUP(MAX($C$21:$E$31)*1,01;1)

For visual purposes, I add 1% to the number by multiplying the 'MIN' formula by 1,01 and I look for the nearest decimal value to which it should round up or down. These formulas work, but under the condition that each cell in the table contains a value. However, the data in the tables depends on the selections made from a list. When one or more selection fields are empty, the tables show zeros, and the Rounddown formula shows a zero as well. I would like this formula to exclude 0, but haven't found a way to achieve this yet.

To automatically change the minimum and maximum values used by a chart, I have used a code I found on the internet:

Sub UpdateScale()

ActiveSheet.ChartObjects("Chart 1").Activate

With ActiveChart.Axes(xlValue)

.MinimumScale = Range("G20").Value

.MaximumScale = Range("G21").Value

End With

ActiveSheet.ChartObjects("Chart 2").Activate

With ActiveChart.Axes(xlValue)

.MinimumScale = Range("G33").Value

.MaximumScale = Range("G34").Value

End With

ActiveSheet.ChartObjects("Chart 3").Activate

With ActiveChart.Axes(xlValue)

.MinimumScale = Range("G46").Value

.MaximumScale = Range("G47").Value

End With

ActiveSheet.ChartObjects("Chart 4").Activate

With ActiveChart.Axes(xlValue)

.MinimumScale = Range("G59").Value

.MaximumScale = Range("G60").Value

End With

ActiveSheet.ChartObjects("Chart 5").Activate

With ActiveChart.Axes(xlValue)

.MinimumScale = Range("G72").Value

.MaximumScale = Range("G73").Value

End With

End Sub

The code functions as well, but has to be executed manually each time the corresponding data for the chart changes. I would like this to be done automatically, so that every time a change is made to the table, the chart automatically uses the new Minimum and Maximum values.

Any ideas on solving these 2 issues?