VBA to skip cell if blank

  • I have what seems like it should be a simple piece of code but I have too many gaps in my own knowledge base to figure out. I have found multiple pieces in my research but have been unable to pull it all together. I need to remove the time stamp from date time values in a column. Some of the cells are blank. I want to do nothing if the cell is blank, if the cell has a date time value I want to strip out the time value, and then continue to the end.




    [VBA]Sub ToDate()
    Dim LR As Long, i As Long
    LR = Range("I" & Rows.Count).End(xlUp).Row
    For i = 2 To LR
    If Not IsEmpty(Cells(i, 2)) Then


    With Range("I" & i)
    .NumberFormat = "mm/dd/yy"
    .Value = CLng(.Value)

    End With
    End If
    Next i


    End Sub[/VBA]


    If anyone can help me correct my code I would appreciate it.


    Thanks!

  • Thanks for the response. It seems to work fine where I have cells with data, but it is populating all blank cells with 01/00/00 for a date. Any ideas on what I am doing wrong to have it skip blank cells (leave them blank)?

  • Maybe change the criteria for empty as such. Another caveat would be if there are spaces, I know one of the systems at my work genrates a lot of extra spaces when exporting to excel, thus the cells may look blank but truly have "data" in them. the below recomendation would fail in that instance.


    [VBA]Sub ToDate()
    Dim LR As Long, i As Long
    LR = Range("I" & Rows.Count).End(xlUp).Row
    For i = 2 To LR
    If Cells(i, 2).value <> "" Then


    With Range("I" & i)
    .NumberFormat = "mm/dd/yy"
    .Value = CLng(.Value)


    End With
    End If
    Next i


    End Sub[/VBA]

  • I appreciate the responses. I have queried the cells and they are showing as truly blank. I am still tweaking it and trying to figure out the problem. I tried adjusting the empty criteria and still have the same problem. I will post an answer if I come up with a solution. Thanks again for everyone's help.

  • Is this data that is imported from another source? If so, there may be unprinted characters in the cells. To check this, I have written a small VBA program to test. It will turn any cells that are not truly empty green. In this manner, if your "empty" cells are truly empty they will stay uncolored