Clever Geek Handbook

Excel VBA deleting rows in for loop skipping rows

I have a routine that deletes lines in a range that contains about 1000 lines. Rows are deleted by criterion. Below is the code.

However, when I run a macro, I usually have to run it 4 times before all lines containing the deletion criteria are deleted.

I'm guessing this is because the for loop skips its index when the row disappears unexpectedly while deleting the row.

My first code looks like this.

    Set StatusRange = Range("B2", Range("B2").End(xlDown))

        For Each StatusCell In StatusRange
                    If StatusCell = "FG" Then
                        StatusCell.EntireRow.Delete
                    ElseIf StatusCell = "QC" Then
                        StatusCell.EntireRow.Delete
                    ElseIf StatusCell = "CS" Then
                        StatusCell.EntireRow.Delete
                    Else
                End If
     Next StatusCell

When I try to update the range of each loop, it still doesn't work.

Set StatusRange = Range("B2", Range("B2").End(xlDown))
     For Each StatusCell In StatusRange
            If StatusCell = "FG" Then
                StatusCell.EntireRow.Delete
            ElseIf StatusCell = "QC" Then
                StatusCell.EntireRow.Delete
            ElseIf StatusCell = "CS" Then
                StatusCell.EntireRow.Delete
            Else
        End If

        Set StatusRange = Range("B2", Range("B2").End(xlDown))
        Next StatusCell

Is there anyone who knows this? Thank you.

+3


source to share


2 answers


Work from the bottom up. If you delete a line, everything moves up and you skip that line on the next iteration.

Here's the guts of the code to process from below.



With Worksheets("Sheet1")
    For rw = .Cells(.Rows.Count, "B").End(xlUp).Row To 2 Step -1
        Select Case UCase(.Cells(rw, "B").Value2)
            Case "FG", "QC", "CS"
                .Rows(rw).EntireRow.Delete
        End Select
    Next rw
End With
+6


source


Since For Each

there is no reverse loop for, you need to use a slightly different approach.

Also, your code is multiple If

and OR

is a shout-out to use Select Case

.



Dim StatusRange As Range
Dim i As Long

Set StatusRange = Range("B2", Range("B2").End(xlDown))

' loop backward when deleting Ranges, Rows, Cells
For i = StatusRange.Rows.Count To 1 Step -1
    Select Case StatusRange(i, 1).Value
        Case "FG", "QC", "CS"
            StatusRange(i, 1).EntireRow.Delete
        Case Else ' for the future if you need it

    End Select
Next i
+1


source






More articles:


All Articles