Deleting rows is not fast enough

Question

I have over 60000 lines in excel I need to weed down by reading the F Column. I have a Functioning Macro but it is taking forever. I am deleting the rows if in Column F the Value is 0 or null. I think my code may be too wordy. I am pretty new at VBA. I found this code online and tried to make it my own. Any help is greatly appreciated. My code is listed below.

Private Sub CommandButton1_Click()
    Dim rng As Range
    Dim i As Long
    Set rng = ThisWorkbook.ActiveSheet.Range("F1:F62000")
    With rng
        For i = .Rows.Count To 1 Step -1
            If .Item(i) = "" Then
                .Item(i).EntireRow.Delete
            End If
            For i = .Rows.Count To 1 Step -1
                If .Item(i) = "0" Then
                  .Item(i).EntireRow.Delete
                End If
            Next i
    End With
End Sub

Also instead of hardcoding `62000` you can find the last row as shown [HERE](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba/11169920#11169920). — Siddharth Rout, Jul 03 '17 at 18:29
I apologise for replying and then closing this question as a duplicate. I didn't know it was a duplicate but later realized it. See my comment below @A.S.H's post below. — Siddharth Rout, Jul 03 '17 at 18:35

score 2 · Answer 1 · answered Jul 03 '17 at 18:21

2

Deleting rows in a loop is a very slow process.

Here is a much faster method. This identifies the range that needs to be deleted and then deletes them in the end in one go. Also you have lot of unnecessary code which I have removed. I have not tested the below code but I think it should work.

Private Sub CommandButton1_Click()
    Dim rng As Range, delRng As Range
    Dim i As Long

    With ThisWorkbook.ActiveSheet
        For i = 1 To 62000
            If .Range("F" & i).Value = "" Or .Range("F" & i).Value = "0" Then
                If delRng Is Nothing Then
                    Set delRng = .Range("F" & i)
                Else
                    Set delRng = Union(delRng, .Range("F" & i))
                End If
            End If
        Next i
    End With

    If Not delRng Is Nothing Then delRng.Delete
End Sub

Note: Another faster method would be to use .Autofilter

answered Jul 03 '17 at 18:21

Siddharth Rout

147,039
17
206
250

I tired the code you suggested but it is deleting the columns not the rows thanks – Bsmith Jul 03 '17 at 18:38
Deleting the columns? My code doesn't delete the columns or the rows... It deletes the cells in Column F. – Siddharth Rout Jul 03 '17 at 18:41
I had made a mistake. Thanks. I had it working but now it is giving me an item mismatched error. Do you have any idea why that would be happening? – Bsmith Jul 03 '17 at 18:47
which line is it giving you the error? – Siddharth Rout Jul 03 '17 at 18:48
The auto filter worked great! Thank you!! – Bsmith Jul 03 '17 at 19:03

score 2 · Answer 2 · edited Jul 03 '17 at 22:18

2

To cope with the possibility of your column F having both numbers and text, you can use Autofilter in two phases: one to delete where blank, and one to delete where 0:

    With ThisWorkbook.ActiveSheet.Range("F1:F62000")
    .AutoFilter 1, ""
    .Offset(1).EntireRow.Delete
    .AutoFilter

    .AutoFilter 1, 0
    .Offset(1).EntireRow.Delete
    .AutoFilter
  End With

From experience this is the fastest method to delete rows by criteria. Notice, though, that using Autofilter needs the first row to be a header row. Insert a blank row if that's not the case.

edited Jul 03 '17 at 22:18

Wolfie

27,562
7
28
55

answered Jul 03 '17 at 18:27

A.S.H

29,101
5
23
50

I was planning to add an Autofilter example as well :D – Siddharth Rout Jul 03 '17 at 18:29
@SiddharthRout Then I saved you some work. Coffee is on you :P – A.S.H Jul 03 '17 at 18:31
Oops I just realised that this question is a duplicate. Your recent edit `From experience this is the fastest method to delete rows by criteria` brought back some memory and when I searched for it, I realized that this question has been asked before. See my comment below Brettdj's post in that question ;) – Siddharth Rout Jul 03 '17 at 18:33
Ah, I didn't benchmark to compare speed of union vs autofilter, only noticed that autofilter is always faster up to some reasonable size. But well, if you did then kudos. On the other hand I wonder how many individual chunks can a union hold... do you know of a limit for that? @SiddharthRout – A.S.H Jul 03 '17 at 18:43
I doubt 60k rows will make any dent to autofilter's speed. You comment was like a blast from the past :D – Siddharth Rout Jul 03 '17 at 18:45
1

I have not extensively tested it but both union vs autofilter will become slower over huge amount of data. However they are much faster than looping. – Siddharth Rout Jul 03 '17 at 18:47
Yeah, that's sure. – A.S.H Jul 03 '17 at 18:49
1

The auto filter worked great! Thank you!! – Bsmith Jul 03 '17 at 19:02

score 0 · Answer 3 · answered Jul 03 '17 at 18:12

0

Try using Application.ScreenUpdating = False before the code and Application.ScreenUpdating = True after it.

You have two For i = in your code. One inside the other... Use only one:

For i = .Rows.Count To 1 Step -1
    If .Item(i) = "" Or .Item(i) = "0" Then
       .Item(i).EntireRow.Delete
    End If
Next i

answered Jul 03 '17 at 18:12

Daniel Möller

84,878
18
192
214

Where you mean before code? I keep putting it after button click but it keeps giving me error "Control Variable already in use" – Bsmith Jul 03 '17 at 18:32
Thanks for the help by the way – Bsmith Jul 03 '17 at 18:33

Deleting rows is not fast enough

3 Answers3