DateDiff gives Type mismatch error 13?

[karinfromsweden]

Hey all,
I have this neat little macro that runs on every start of a sheet. It's (supposed to be) pretty simple, checking for rows that are older than 180 days and then deleting part of the content of the row. My boss was so happy over this GDPR-compliant macro.
But then a few days ago it started giving "run time error 13 Type mismatch".

I have doublechecked that the column only holds correctly formatted dates. And from what I can see, the macro works. So why does it give an error?
Any help would be dearly appriciated! Thank you!

Sub ClearCells()
Dim Cel As Range, Ws As Worksheet
Set Ws = Sheets("START")
For Each Cel In Ws.Range("A5", Ws.Range("A" & Rows.Count).End(xlUp))
    If DateDiff("d", Cel, Date) > 180 And Cel.Offset(, 0) <> "" Then Cel.Offset(, 6).Resize(, 3).ClearContents
Next Cel
End Sub

 

[Fennek]

Hi Karin, without any tests: Have you checked, that there are only dates in column "A"? regards

 

[Norie]

When you get the error what is the value in Cel?

 

[Yongle]

try running this to tell you which cells have got problems

Sub ClearCells()
    Dim Cel As Range, Ws As Worksheet, Msg As String
    Set Ws = Sheets("START")
    For Each Cel In Ws.Range("A5", Ws.Range("A" & Rows.Count).End(xlUp))
        On Error Resume Next
        If DateDiff("d", Cel, Date) > 180 And Cel.Offset(, 0) <> "" Then Cel.Offset(, 6).Resize(, 3).ClearContents
        If Err.Number > 0 Then Msg = Msg & vbCr & Cel.Address & vbTab & Cel.Value
        On Error GoTo 0
    Next Cel
    MsgBox Msg, vbCritical, "Problem cells"
End Sub

 

[Yongle]

Why use Cel.Offset(, 0) ?
Without any offset in any direction it is the same as Cel

 

[karinfromsweden]

try running this to tell you which cells have got problems

Sub ClearCells()
    Dim Cel As Range, Ws As Worksheet, Msg As String
    Set Ws = Sheets("START")
    For Each Cel In Ws.Range("A5", Ws.Range("A" & Rows.Count).End(xlUp))
        On Error Resume Next
        If DateDiff("d", Cel, Date) > 180 And Cel.Offset(, 0) <> "" Then Cel.Offset(, 6).Resize(, 3).ClearContents
        If Err.Number > 0 Then Msg = Msg & vbCr & Cel.Address & vbTab & Cel.Value
        On Error GoTo 0
    Next Cel
    MsgBox Msg, vbCritical, "Problem cells"
End Sub

Wow! This gave me an empty cell, but I did Clear > All and now it works. Weird. THANK YOU!

 

[Yongle]

Not all empty-looking cells are truly empty!
Possibly contained an errant space