Adding minutes to all cells in a column


I need your assistance and it would be greatly appreciated. I need a VBA program to add certain amount of minutes to columns containing times.  I have a sheet containing multiple columns and they all have times in them. I want to add a constant amount of minutes to the time in each cell in the column. The old time would be overwritten with the new calculated time. Thank you for all you help.



Thank you for your suggestion but I need to do it programmatically. I am already aware of your method. This is the initial phase and then I need to do some other manipulation to the column times. Please provide me a VBA macro that will do it.

Thank you
zman1234 Jan 28, '18 at 4:44 pm
Add to Discussion



You don't need VBA for that. Follow these steps instead.

  1. Write the time you want added in a blank cell on your sheet.
  2. On the Ribbon's Home tab press Copy (or key in Ctl+C)
  3. Select the cells to which you wish to add the time you wrote in step 1 - one cell, one column or several columns, doesn't matter. If the range is very big, use Find & Select > Goto to specify it.
  4. From the Home tab on the ribbon press Paste
  5. In the dialog box that opens, select Paste special
  6. In the Paste special dialog box select Add
  7. Press OK to carry out the requested calculation on the selected cells.
  8. Clear the cell you used to hold the value to be added.

Since you insist on code, here is the code. You need to change the values of the three constants at the top of the procedure for each run of it.

Option Explicit
Sub AddMinutes()
    ' 29 Jan 2018
    Const Minutes As Single = 30                    ' change as required
    Const PlusMinus As String = "plus"              ' any string <> "plus" = minus
    Const Target As String = "B"                    ' can also take "B:D" or "C2:D200"
    Dim MRng As Range
    Dim Plus As Integer
    Dim Tgt As String
    Dim Rng As Range
    Dim R As Long, Rl As Long
    Application.ScreenUpdating = False
    Plus = IIf(InStr(1, "Plus", PlusMinus, vbTextCompare) = 1, 1, -1)
    Tgt = Target
    If InStr(Tgt, ":") = 0 Then Tgt = Tgt & ":" & Tgt
    Set Rng = Range(Tgt)
    For Each MRng In Rng.Columns
        R = Cells(Rows.Count, MRng.Column).End(xlUp).Row
        If R > Rl Then Rl = R
    Next MRng
    With Rng
        R = Application.Min(.Row + .Rows.Count - 1, Rl)
        Set Rng = Range(Cells(.Row, .Column), Cells(R, .Column + .Columns.Count - 1))
        Set MRng = Cells(Rows.Count, .Column).End(xlUp).Offset(1)
    End With
    With MRng
        .NumberFormat = Rng.Cells(1).NumberFormat
        .Value = Minutes / 24 / 60 * Plus
    End With
    ' set SkipBlanks to False if you want to write the added minutes
    ' also in cells which were blank before
    Rng.PasteSpecial Paste:=xlPasteAll, _
                     Operation:=xlAdd, _
                     SkipBlanks:=True, _
    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
    End With
End Sub

Answer the Question

You must create an account to use the forum. Create an Account or Login