Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Add a leading zero to all cells that contain a colon


I have a spreadsheet where I export time data from another program. The new cells are automatically formated by excel into the h:mm format.  After the data is pasted, I have a formula set up in another cell to convert it to seconds. 

The problem I am encountering is that if a value is under 1:00 excel will convert the cell to the general format, so my conversion formula will display a #VALUE error.  I can maunually had a 0 before the colon and excel will convert it to seconds without any issues. 

Is there any way to automatically add a leading zero to cells that contain a colon? 



Selected Answer

You might set the number format property after pasting the data.

With ActiveSheet.Columns("B")

    .Cells.Numberformat = "HH:mm:ss"

End With

I'm surprised this doesn't work for you. However, the following is an adaption of your code which you say does work to allow you to specify the columns you want reformatted. Just modify the split string "Clm" to your needs.

Sub LeadingZero()
    ' LeadingZero Macro
    Dim rngData As Range
    Dim C As Long
    Dim Clms() As String
    Dim i As Integer
    Clms = Split("D,F,H,J,L", ",")
    For i = 0 To UBound(Clms)
        C = Columns(Clms(i)).Column
        Set rngData = Range(Cells(3, C), Cells(Rows.Count, C).End(xlUp))
        Debug.Print rngData.Address
        With rngData
            .Value = Evaluate("IF(ROW(" & .Address & "),IF(ISNUMBER(" & .Address & ")," _
                               & .Address & ",0+""00""&" & .Address & "))")
        End With
    Next i
End Sub


I tried changing just the format without adding a zero before it, but excel still wouldn't convert it to seconds.  I found this code online, which works, but I don't know how to set it up for multiple columns.  I was able to make it work by adding a helper column, but I have some other spreadsheets with the same issue, and a helper column wouldn't work on those sheet.  Do you know how I would change the code to only change colums D, F, H, J, and L?  

Sub LeadingZero() ' ' LeadingZero Macro ' Dim rngData As Range Set rngData = Range(Cells(3, "A"), Cells(Rows.Count, "A").End(xlUp)) With rngData     .Value = Evaluate("IF(ROW(" & .Address & "),IF(ISNUMBER(" & .Address & ")," & .Address & ",0+""00""&" & .Address & "))") End With End Sub
Harrison (rep: 2) Apr 17, '17 at 3:03 pm
That works perfectly, thanks for the help!
Harrison (rep: 2) Apr 18, '17 at 1:15 pm
Add to Discussion

Answer the Question

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