Improving a Loop to delete rows in excel faster

Question

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 fastest way would be to select all the rows first then delete them at once using Range.Delete. It's the delete operation that takes the most time. — John Wu
– John Wu, Commented Sep 6, 2017 at 14:22
Just adding to John Wu's answer, your range might be non contiguous, so you might need to create a non contiguous range — danieltakeshi
– danieltakeshi, Commented Sep 6, 2017 at 14:26
Also use Application.ScreenUpdating = False before the loop (and don't forget to turn it back on afterwards) — Tragamor
– Tragamor, Commented Sep 6, 2017 at 16:36

Darrell H · Accepted Answer · 2017-09-06 14:30:04Z

4

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

answered Sep 6, 2017 at 14:30

Darrell H

1,8961 gold badge11 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tom · Accepted Answer · 2017-09-06 14:29:18Z

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

Jeremy · Accepted Answer · 2017-09-06 15:00:47Z

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

answered Sep 6, 2017 at 15:00

Jeremy

1,3373 gold badges13 silver badges28 bronze badges

Collectives™ on Stack Overflow

Improving a Loop to delete rows in excel faster

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related