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)?

  • are they truly empty, ie. no formulas? Because if there is a formula there that returns a blank, then in Excel it is not empty. If there is no value in Column I, what date do you want returned.

  • 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


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!