VBA code to delete extra rows in sheet with thousands of rows slowed significantly

Question

We have an Excel macro that cleans up a spreadsheet containing thousands of rows. It starts from the bottom of the sheet and deletes rows that meet certain criteria.

I am told that this macro used to take a few minutes to run, and now it takes an hour. It used to zip up the spreadsheet, and now it takes about a second per row, which obviously adds up.

We recently upgraded from Excel 2007 to Excel 2016, so I am not sure if that is the cause. I have tried the macro on multiple computers and it is slow on all of them, so I don't think it's a faulty install issue. It may just be that the code is written inefficiently, or the spreadsheets have gotten larger. Not sure what else would cause this change.

Here is the code:

Sub DeleteExtraRows()
Dim RowCount As Integer
Dim i As Integer

RowCount = ActiveSheet.Cells(Rows.count, "B").End(xlUp).row
'Delete the Rows
For i = RowCount To 2 Step -1
    Range("A" & i).Select
    If (Range("A" & i).Style = "Neutral" And Range("AC" & i) = False) Or (Range("U" & i) = 1 And Range("V" & i) = 0 And Range("AC" & i) = False) Then
        Rows(i).Delete
    End If
    Application.StatusBar = RowCount - i & " of " & RowCount & " Records Processed"
Next i
'Delete all the checkboxes
ActiveSheet.CheckBoxes.Delete
Range("A:A").Delete
Application.StatusBar = False
'Move to the top
Range("A2").Select
End Sub

[Use `Union` when deleting](https://stackoverflow.com/questions/59975239/for-loop-not-fully-cycling-in-excel-vba). — BigBen, Mar 26 '21 at 18:13
[Use `Long` instead of `Integer`](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long) as well - won't speed things up, but could prevent an Overflow error. — BigBen, Mar 26 '21 at 18:13
It may be that the used cells are much more extensive than you think they are. For example if you set a column fill colour the sheet size expands to the millions downwards. The fix is to delete the empty cells. — user10186832, Mar 26 '21 at 18:43

score 0 · Answer 1 · answered Mar 26 '21 at 18:27

Delete Thousands of Rows

I recently encountered a code that used the status bar in a similar way and slowed down the code dozens of times. Don't do that if it's not necessary.
Not tested.

Option Explicit

Sub DeleteExtraRows()
    
    With ActiveSheet
        
        'Delete the rows
        Dim RowCount As Long: RowCount = .Cells(.Rows.Count, "B").End(xlUp).Row
        Dim drg As Range
        Dim i As Long
        For i = 2 To RowCount
            If (.Range("A" & i).Style = "Neutral" And .Range("AC" & i) = False) _
                    Or (.Range("U" & i) = 1 And .Range("V" & i) = 0 _
                    And .Range("AC" & i) = False) Then
                If drg Is Nothing Then
                    Set drg = .Rows(i)
                Else
                    Set drg = Union(drg, .Rows(i))
                End If
            End If
        Next i
        If Not drg Is Nothing Then
            drg.Delete
        End If
        
        'Delete all the checkboxes
        .CheckBoxes.Delete
        
        .Range("A:A").Delete
        
        'Move to the top
        .Range("A2").Select
    
    End With
    
End Sub

As a slight addition to this answer: keep track of how big your `union`ed `drg` is; iirc it can become too big to add even more to. If so, that probably has to do with lots of non-unionable rows, making for a range representing a lot of disjoined areas. The trick is to find a sweet spot of, say, 100 rows (that should already give quite a large performance boost), and if you've added more, then just delete the `drg` at that spot and start over with an empty `drg`. — Carl Colijn, Mar 26 '21 at 19:44

VBA code to delete extra rows in sheet with thousands of rows slowed significantly

1 Answers1

Delete Thousands of Rows