VBA to skip cell if blank

wid2001 · Jun 8th 2018

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!

**alansidman** · Jun 8th 2018

So long as there is data in Column I, it works for me. You may want to change the column,however that searches for the last row.

wid2001 · Jun 8th 2018

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

**alansidman** · Jun 8th 2018

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.

chavezm3 · Jun 8th 2018

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]

wid2001 · Jun 9th 2018

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.

**alansidman** · Jun 9th 2018

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

Code

Option Explicit


Sub IsEmpty()
    Dim i As Long, lr As Long
    lr = Range("B" & Rows.Count).End(xlUp).Row
    For i = 2 To lr
        If Len(Range("B" & i)) > 0 Then
            Range("B" & i).Interior.ColorIndex = 4
        End If
    Next i
End Sub

Display More

VBA to skip cell if blank

Participate now!

Share

Users Viewing This Thread