CODEWITHSUNDEEP
excelvba
isaaacs
isaaacs

Reputation: 89

VBA Error while trying to delete rows that contain 0

I am creating a macro to delete every row where the value of its cell in column AN is 0.

This is the code I used:

Sub DeleteRow()

Dim lastrow As Long, r As Long

lastrow = Cells(Rows.Count, "AN").End(xlUp).Row

For r = lastrow To 2 Step -1
If Cells(r, "AN") = 0 Then
Rows(r).EntireRow.Delete
End If
Next r

End Sub

This code produces a 'mismatch' error for the "If Cells(r, "AN") = 0 Then" line. How can I fix this?

Thank you

Upvotes: 1

Views: 294

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71217

I would use Range instead of Cells.

If [ActiveSheet.]Range("AN" & r) = 0 Then

Note that you should always qualify Range, Cells, Rows, Columns, and Names member calls with a proper Worksheet object reference - otherwise you are implicitly working off whatever worksheet happens to be the ActiveSheet, and that has a tendency to cause errors whenever the active sheet isn't the one that the code is assuming. Unless the code is written in a worksheet's code-behind - in which case the implicit qualifier is Me, and it doesn't hurt to make it explicit.

But that won't solve the problem.

The problem is that you are assuming that the cell you're reading contains a value that is legally comparable to 0. When comparing a cell value to 0 or "" throws a type mismatch error, look at the worksheet: in all likelihood there are #N/A or #VALUE! worksheet errors.

Worksheet error values are a special data type: Variant/Error, and values of that data type cannot be compared to anything other than Variant/Error values - a type mismatch error is raised otherwise.

So, make sure you're not looking at a cell error before you do anything with the value:

If Not IsError([ActiveSheet.]Range("AN" & r).Value Then
    'safe to compare
    If [ActiveSheet.]Range("AN" & r).Value = 0 Then
       '...
    End If
Else
    Debug.Print "'AN" & r & "' contains a cell error"
End If

Upvotes: 2

Related Questions