Excel VBA 在 for 循环中删除行会丢失行 [英] Excel VBA deleting rows in a for loop misses rows

问题描述

我有一个子程序，用于删除包含大约 1000 行的范围内的行.根据条件删除行.下面的代码有效.

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

但是，当我运行宏时，我通常必须运行它 4 次才能删除包含删除条件的所有行.

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

我猜这是因为当删除一行时一行突然消失时，for 循环错过了它的索引.

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 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
        

有人知道这个吗?

推荐答案

自下而上工作.如果您删除一行，所有内容都会向上移动，您会在下一次迭代中跳过该行.

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

这是从底层开始工作的代码的胆量".

Here is the 'guts' of the code to work up from the bottom.

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

这篇关于Excel VBA 在 for 循环中删除行会丢失行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！