Improving a Loop to delete rows in excel faster

问题:

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

回答1:

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

回答2:

Assign all of the rows that you want to delete to a range and then delete the range in one go. This is much faster then deleting one by one and prevents errors.

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

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
        If DelRng Is Nothing Then
            Set DelRng = Cells(IRow, 1)
        Else
            Set DelRng = Union(DelRng, Cells(IRow, 1))
        End If
    Else
        IRow = IRow + 1                     ' move to next row
    End If
Loop

If Not DelRng Is Nothing Then DelRng.EntireRow.Delete

回答3:

I'd filter for 0 first and then delete all visible rows:

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

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

ActiveSheet.Range("A1:BD1").AutoFilter Field:=colBD, Criteria1:="0"
Range("BD" & IRow & ":BD" & LstRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete