Round times in multiple cells VBA

Question

I need to round the times to the nearest minute in about 40k cells. 20k cells in column I and 20k cells in column J. I do not want a separate column with the value rounded up or down in as this will look scruffy and I do not need the original values. I also need to do this every month on different spread sheets and for various people, so I thought VBA and add a button. I have worked out the code I need to do on each cell but it will be very tedious to do this for all 40k. Is there a way to shorten my code?

J4 = Range("J4")

J4 = Application.WorksheetFunction.MRound(J4, "0:01")

Range("J4").Value = J4

J5 = Range("J5")

J5 = Application.WorksheetFunction.MRound(J5, "0:01")

Range("J5").Value = J5

J6 = Range("J6")

J6 = Application.WorksheetFunction.MRound(J6, "0:01")

Range("J6").Value = J6

Thank you for any help

not using the worksheet function and using VBA's `ROUND` would speed things up quite a bit. [this](https://stackoverflow.com/questions/326476/rounding-a-number-to-the-nearest-5-or-10-or-x) may be of use. You will also want to look into looping — cybernetic.nomad, Nov 28 '19 at 18:34

score 0 · Accepted Answer · answered Nov 28 '19 at 18:58

0

Please try this

Sub RoundCells()
    Dim Rng As Range, c As Range
    Set Rng = Range("I4:J20000")
    For Each c In Rng
        c.Value = Application.WorksheetFunction.MRound(c, "0:01")
    Next
End Sub

answered Nov 28 '19 at 18:58

Mesut Akcan

899
7
19

Round times in multiple cells VBA

1 Answers1