Keep zero baseline in charts

[Factotum]

I've done a lot with VBA, but never anything relating to charts. I have a macro, courtesy of www.TheSpreadsheetGuru.com, which adjusts the Min/Max of the Y-Axis of a chart (see below). I would like to make it so that a zero baseline is always visible. If the data points go below 0, then the zero baseline would be somewhere in the middle of the graph. If the data points do not go below zero, then the bottom of the Y-Axis would begin at zero, regardless what the max is. Any suggestions?

As a bonus: any suggestions on how to always round the max up to the nearest 100?

Here's the code I have:

Dim cht As ChartObject
Dim srs As Series
Dim FirstTime  As Boolean
Dim MaxNumber As Double
Dim MinNumber As Double
Dim MaxChartNumber As Double
Dim MinChartNumber As Double


With Worksheets("KPMs 2018").ChartObjects("Chart 1")
    .Activate


    'Determine Chart's Overall Max/Min From Connected Data Source
      For Each srs In ActiveChart.SeriesCollection
        'Determine Maximum value in Series
          MaxNumber = Application.WorksheetFunction.Max(srs.Values)
        
        'Store value if currently the overall Maximum Value
          If FirstTime = True Then
            MaxChartNumber = MaxNumber
          ElseIf MaxNumber > MaxChartNumber Then
            MaxChartNumber = MaxNumber
          End If
        
        'Determine Minimum value in Series (exclude zeroes)
          MinNumber = Application.WorksheetFunction.Min(srs.Values)
          
        'Store value if currently the overall Minimum Value
          If FirstTime = True Then
            MinChartNumber = MinNumber
          ElseIf MinNumber < MinChartNumber Or MinChartNumber = 0 Then
            MinChartNumber = MinNumber
          End If
End With


    'Rescale Y-Axis
    ActiveChart.Axes(xlValue).MinimumScale = Round(MinChartNumber / 100) * 100
    ActiveChart.Axes(xlValue).MaximumScale = Round(MaxChartNumber / 100) * 100

 

[Factotum]

Looks like I should do my homework before agreeing to write a macro to do what the user wants. Turns out that what I was trying to do, and eventually succeeded in, is already an available function in Excel. All that is needed is to right click on the axis, select Format Axis from the drop down, and set the Minimum and Maximum to 'Auto'. In case anyone is interested in how I did this with VBA, I just added an IF/THEN to the original code (see below). I did not find out how to always round up, so I just rounded to the nearest hundred and added an extra 500 to the axis to make sure the rounding did not take the graph off the chart.

Sub Chart_Update()


Dim cht As ChartObject
Dim srs As Series
Dim FirstTime  As Boolean
Dim MaxNumber As Double
Dim MinNumber As Double
Dim MaxChartNumber As Double
Dim MinChartNumber As Double


With Worksheets("KPMs 2018").ChartObjects("Chart 1")
    .Activate


    'Determine Chart's Overall Max/Min From Connected Data Source
      For Each srs In ActiveChart.SeriesCollection
        'Determine Maximum value in Series
          MaxNumber = Application.WorksheetFunction.Max(srs.Values)
        
        'Store value if currently the overall Maximum Value
          If FirstTime = True Then
            MaxChartNumber = MaxNumber
          ElseIf MaxNumber > MaxChartNumber Then
            MaxChartNumber = MaxNumber + 500
          End If
        
        'Determine Minimum value in Series (exclude zeroes)
          MinNumber = Application.WorksheetFunction.Min(srs.Values)
          
        'Store value if currently the overall Minimum Value
          If FirstTime = True Then
            MinChartNumber = MinNumber
          ElseIf MinNumber < MinChartNumber Or MinChartNumber = 0 Then
            MinChartNumber = MinNumber
          End If
        
      Next srs
End With


    'Rescale Y-Axis
    If MinChartNumber > 0 Or MinChartNumber = 0 Then
        ActiveChart.Axes(xlValue).MinimumScale = 0
    Else
        ActiveChart.Axes(xlValue).MinimumScale = Round(MinChartNumber / 100) * 100 - 500
    End If
    
    ActiveChart.Axes(xlValue).MaximumScale = Round(MaxChartNumber / 100) * 100


End Sub