改善循环以更快地删除Excel中的行 [英] Improving a Loop to delete rows in excel faster

问题描述

如果BD列中的值为零，我创建了一个删除行的循环。循环最多可能需要半小时。我看到了使用自动过滤器或创建变体数组以加快功能的建议。让我知道您是否可以为下面的代码提出更好的解决方案。

I have created a loop to delete rows if the value in column BD is zero. The loop can take up to a half hour. I have seen recommendations to use autofilters or create variant arrays to expedite the function. Let me know if you can suggest a better solution for my code below.

Const colBD As Long = 56
Dim IRow     As Long
Dim LstRow  As Long


LstRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
IRow = 3

'loop to delete rows with zero in colBD
Do While IRow <= LstRow
    If Cells(IRow, colBD) = 0 Then
        Cells(IRow, 1).EntireRow.Delete
        LstRow = LstRow - 1             ' one less row
    Else
        IRow = IRow + 1                     ' move to next row
    End If
Loop

推荐答案

上面的评论是正确的，最好删除所有立即执行，但是如果要循环播放，则最好从头开始并重新进行。

The comment above is correct, better to delete all at once, however if looping it best to start at the end and work back. It easy to get in an endless loop.

Const colBD As Long = 56
Dim IRow As Long
Dim LstRow As Long
Dim i As Integer

LstRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
IRow = 3

'Always start with the last row and work towards the first when deleting rows
For i= LstRow to IRow Step - 1
    If Cells(i, colBD) = 0 Then
        Cells(i, 1).EntireRow.Delete
    End If
End Sub

这篇关于改善循环以更快地删除Excel中的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！