在for for循环中删除行的Excel VBA缺少行 [英] Excel VBA deleting rows in a for loop misses rows
问题描述
在critera上删除行。
下面的代码工作。
但是,当我运行宏时,我通常必须运行它4次,然后删除包含删除条件的所有行。
我猜这是因为当删除行时,行突然消失时,for循环错过了它的索引。
我的第一个代码看起来像这样。
Set StatusRange = Range(B2,Range(B2)。End(xlDown))
每个StatusCell在StatusRange
如果StatusCell =FG然后
StatusCell.EntireRow.Delete
ElseIf StatusCell =QC然后
StatusCell.EntireRow.Delete
ElseIf StatusCell =CS然后
StatusCell.EntireRow.Delete
Else
结束如果
下一个StatusCell
当我尝试更新每个循环的范围时,它仍然不起作用。
Set StatusRange = Range(B2,Range(B2)。End(xlDown))
每个StatusCell在StatusRange
如果StatusCell =FG然后
StatusCell.EntireRow.Delete
ElseIf StatusCell =QC然后
StatusCell.EntireRow.Delete
ElseIf StatusCell =CS然后
StatusCell.EntireRow.Delete
Else
结束If
设置StatusRange =范围(B2,范围(B2)。 (xlDown))
下一个StatusCell
有没有人知道这个?谢谢。
从下往上工作。如果您删除一行,则所有内容都会向上移动,您可以在下一次迭代中跳过该行。
这是从底部处理的代码的胆量。
使用工作表(Sheet1)
对于rw = .Cells(.Rows.Count,B) .End(xlUp).Row To 2 Step -1
选择案例UCase(.Cells(rw,B)。Value2)
案例FG,QC,CS
.Rows(rw).EntireRow.Delete
结束选择
下一步rw
结束
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.
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
这篇关于在for for循环中删除行的Excel VBA缺少行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!