VBA: If statement in for loop

Question

I seem to get a mismatch error when trying to write an if statement in a for loop.

This is the piece of code where I get the error. Note, I only get the error when the IF condition is true.

Dim lastRow2 As Long
lastRow2 = Worksheets("csvFile").Cells(rows.Count, 1).End(xlUp).Row

Dim r As Integer
For r = 3 To lastRow2
    If Worksheets("csvFile").Cells(r, 1) = "#N/A" Then
        rows(r).EntireRow.delete
    End If
Next r

So the goal is to delete the row where in the first cell "#N/A" is entered as a value.

Hope you guys can help and loads of thanks in advance.

Tried it, unfortunately doesn't work. Maybe good to add (will add to opening post too) is that I only get the error when the condition is true. — YellowSkin
– YellowSkin, Commented Nov 29, 2017 at 23:54
Have you tried double equals: If Worksheets("csvFile").Cells(r, 1) == "#N/A" Then — kurdtpage
– kurdtpage, Commented Nov 29, 2017 at 23:56
I need to stop commenting before coffee... Possible duplicate of Excel VBA - Interpret "N/A" Values — Robbie
– Robbie, Commented Nov 30, 2017 at 0:01

tlemaster · Accepted Answer · 2017-11-30 00:00:34Z

1

Give this try:

If WorksheetFunction.IsNA(Worksheets("csvFile").Cells(r, 1)) Then

answered Nov 30, 2017 at 0:00

tlemaster

8595 silver badges7 bronze badges

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

3 Comments

YellowSkin Over a year ago

Thank you, this seems to work. I'll test it a little more.

tlemaster Over a year ago

Don't know what I'm doing differently, but it deletes the row for me.

YellowSkin Over a year ago

I was mistaken, it does so for me too. Thanks!

Shai Rado · Accepted Answer · 2017-11-30 09:05:43Z

In order to see if a cell contains #NA you need to trap this type of error, which is a 2-Steps If.

Also, allways loop backwards when deleting Rows, use For r = lastRow2 To 3 Step -1.

Try the code below, explanations inside the code's comments:

Option Explicit

Sub DelNARows()

Dim lastRow2 As Long
Dim r As Long
Dim CellVal As Variant

With Worksheets("csvFile") ' use with statement to qualify all Range, Rows and Cells object nested inside
    lastRow2 = .Cells(.Rows.Count, 1).End(xlUp).Row

    For r = lastRow2 To 3 Step -1 ' always loop backward when deleting rows
        ' trap #NA error section
        On Error Resume Next
        CellVal = .Cells(r, 1).Value
        On Error GoTo 0

        If IsError(CellVal) Then
            If CellVal = CVErr(xlErrNA) Then ' check if current error if xlErrNA (2042)
                .Rows(r).Delete
            End If
        End If
    Next r
End With

End Sub

Edit 1: a faster way to delete multiple rows, is to delete all the rows at one shot, not one-by-one. You can achieve that by merging all the rows to be deleted in a DelRng (it's a Range object which is combined from all the rows that needs to be deleted).

Code

For r = lastRow2 To 3 Step -1
    ' trap #NA error section
    On Error Resume Next
    CellVal = .Cells(r, 1).Value
    On Error GoTo 0

    Dim DelRng As Range ' define a new range to save all the rows to delete

    If IsError(CellVal) Then
        If CellVal = CVErr(xlErrNA) Then ' check if current error if xlErrNA (2042)
            If Not DelRng Is Nothing Then
                Set DelRng = Application.Union(DelRng, .Rows(i))
            Else
                Set DelRng = .Rows(i)
            End If
        End If
    End If
Next r

' delete the entire rows at once
If Not DelRng Is Nothing Then DelRng.Delete

Collectives™ on Stack Overflow

VBA: If statement in for loop

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related