Excel VBA chart change Y axis labels

[Human_doing]

Hi all,

The below VBA creates a chart from data that may be variable, however when there are very few results, I would like the Y axis to still only have labels for whole numbers i.e. if there is only 1 result I'd like the labels to be 0 and 1 rather than 0, 0.2, 0.4, 0.6, 0.8 & 1, can anyone please help with the VBA to produce this, I am assuming it may only be one additional line required?

Thanks

Sub ChartMachine()
    Dim ws As Worksheet
    Dim Rng As Range
    Dim Rng1 As Range
    For Each ws In Worksheets
        Set Rng = ws.Range("R1:U25")
        Set Rng1 = ws.Range("A25:P36")
        With ws.ChartObjects.Add _
            (Left:=Rng1.Left, Width:=Rng1.Width, Top:=Rng1.Top, Height:=Rng1.Height)
            With .Chart
                .SetSourceData Source:=ws.Range("R1:U25")
                .ChartType = xlColumnClustered
                .HasTitle = True
                .ChartTitle.Characters.Text = ws.Range("A2") & " Time of Occurence 01/04/2009 - 31/03/2012"
                .SeriesCollection(1).Name = "=""Occurences"""
 
 
                    With .PlotArea.Border
                    .ColorIndex = 16
                    .Weight = xlThin
                    .LineStyle = xlContinuous
                End With
                With .PlotArea.Interior
                    .ColorIndex = 2
                    .PatternColorIndex = 1
                    .Pattern = xlSolid
                End With
 
            End With
        End With
    Next ws
 
End Sub

 

[Human_doing]

Just as a side note, I tried adding line:

.Axes(xlCategory).TickLabels.NumberFormat = "0"

And the macro still ran but didn't actually make the necessary changes?

Thanks