Excel VBA deleting rows in a for loop misses rows

Refresh

December 2018

Views

4.6k time

3

I have a subroutine that deletes rows in a range containing around 1000 rows. Rows are deleted on a critera. The code below works.

However, when I run the macro I usually have to run it 4 times before all rows containing the removal criteria are removed.

I guess this is because the for loop misses its index when a row suddenly dissapears when deleting a 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 each loop, it still doesnt 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 know a sloution to this? Thanks.

2 answers

8

Работа снизу вверх. При удалении строки, все движется вверх и пропустить эту строку на следующей итерации.

Вот это «кишки» кода для работы со дна.

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
1

Так как нет обратной петли для For Eachвам нужно использовать несколько иной подход.

Кроме того , ваш код с несколькими Ifс и ORбудет «кричать для использования 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