Type Mismatch error when running VBA code to delete rows based on specific criteria

Question

I'm trying to delete some rows in an Excel sheet based on some specific criteria. At some point, it gives the following Type Mismatch error:

As far as I understand from the partially created sheet, the error occurs inside DO loop at

Or Len(Cells(r, 1)) = 5 _
Or Int(Right(Cells(r, 1), 4)) > 4000 _
Or Cells(r, 3) = 0

part. What should I do to correct the error? Additionally, if you can recommend me an improvement in the code for a faster run, I'd really appreciate it. The full code is as follows:

Sub delrows()

Dim r, RowCount As Long
r = 2

ActiveSheet.Columns(1).Select
RowCount = UsedRange.Rows.Count
userresponse = MsgBox("You have " & RowCount & " rows", vbOKOnly, "Info")

Rows(RowCount).Delete Shift:=xlUp

' Trim spaces

Columns("A:A").Select
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, searchFormat:=False, _
    ReplaceFormat:=False

' Delete surplus columns

Range("L:T,V:AA,AE:AG,AR:AR,AU:AU,AZ:AZ").Select
    Selection.Delete Shift:=xlToLeft

' Delete surplus rows

Do
    If Left(Cells(r, 1), 1) = "D" _
       Or Left(Cells(r, 1), 1) = "H" _
       Or Left(Cells(r, 1), 1) = "I" _
       Or Left(Cells(r, 1), 2) = "MD" _
       Or Left(Cells(r, 1), 2) = "ND" _
       Or Left(Cells(r, 1), 3) = "MSF" _
       Or Left(Cells(r, 1), 5) = "MSGZZ" _
       Or Len(Cells(r, 1)) = 5 _
       Or Int(Right(Cells(r, 1), 4)) > 4000 _
       Or Cells(r, 3) = 0 Then
     Rows(r).Delete Shift:=xlUp
    Else: r = r + 1
    End If
Loop Until (r = RowCount)

End Sub

Answer 1

The condition Or Int(Right(Cells(r, 1), 4)) is a concern, because it assumes that the portion of the cell contents that it's evaluating is a number.

If it turns out not to be a number, then the Int() function would throw the kind of type mismatch error you are seeing.

It would be better if you tested for whether it is in fact a number first, before applying the Int() function. You can use the IsNumeric() function to do so.