CODEWITHSUNDEEP
excelvba
Tourless
Tourless

Reputation: 27

For Loop leaves unwanted rows behind

...because the row is only evaluated once and the next row is called for evaluation. But the next row is now the previous row. How do I account for this?

For i = 5 To Range("A" & "65536").End(xlUp).Row Step 1
If Application.WorksheetFunction.CountIf(Range("A" & i), "#N/A") = 1 Then
Range("A" & i).EntireRow.Delete
End If
Next i

Upvotes: 1

Views: 32

Answers (2)

CallumDA
CallumDA

Reputation: 12113

You can delete your rows all at once, using Union. Like this:

Sub test()
    Dim i As Long
    Dim deleteRange As Range

    For i = 5 To Range("A" & "65536").End(xlUp).Row Step 1
        If Application.WorksheetFunction.CountIf(Range("A" & i), "#N/A") = 1 Then
            If deleteRange Is Nothing Then
                Set deleteRange = Range("A" & i).EntireRow
            Else: Set deleteRange = Union(deleteRange, Range("A" & i).EntireRow)
            End If
        End If
    Next i

    deleteRange.Delete
End Sub

Upvotes: 2

SJR
SJR

Reputation: 23081

Loop backwards (and use Rows.Count rather than hard-coding 65536) as new versions of Excel have a capacity of more than a million rows.

For i = Range("A" & Rows.Count).End(xlUp).Row To 5 Step -1
    If Application.WorksheetFunction.CountIf(Range("A" & i), "#N/A") = 1 Then
        Range("A" & i).EntireRow.Delete
    End If
Next i

Upvotes: 1

Related Questions