excelvbatype-mismatch

Delete the contents of the cell in case of type-mismatch error

An examined column may contain incorrect dates.
For example: 2022.04-05 (April-May) or text, so in case of CDate I get type-mismatch error.

Is there any possibility to delete cells in case of type-mismatch error without interrupting the macro?
I mean if CDate is not possible, delete the contents of the cell.

Solution

  • Using IsDate:

    Returns True if the expression is a date or is recognizable as a valid date or time; otherwise, it returns False.

    If IsDate(cell.Value) Then
   ' collect how many are older than 1 year
Else
   cell.ClearContents
End If