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

Chamila Chulatunga · Accepted Answer · 2012-12-07 14:31:54Z

4

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.

answered Dec 7, 2012 at 14:31

Chamila Chulatunga

4,95418 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related