VBA macro crashing Excel

Question

Hi fellow communiteers,

I'm running a macro to delete entire rows that contain a certain value. The code works fine on small data sets but on the current one (~22,000 records) it consistently crashes Excel (2010). The code is below. Short of splitting the data into smaller chunks and running the macro again and again I'm not sure what to do.

Any help appreciated and here's the code:

Sub CleanOcc()

'Row counting
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim Lrow2 As Long

With Sheets("Occ_Prep")

    'Cleans the occ_prep sheet ready for upload (Column and value can be changed)
    Sheets("Occ_Prep").Activate

    'Set the first and last row to loop through
    Firstrow = .UsedRange.Cells(1).Row
    Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

    'We loop from Lastrow to Firstrow (bottom to top)
    For Lrow2 = Lastrow To Firstrow Step -1

        'We check the values in the A column in this example
        With .Cells(Lrow2, "K")


            If Not IsError(.Value) Then

                If .Value = "0" Then .EntireRow.Delete
                'This will delete each row with the Value "ron"
                'in Column A, case sensitive.

            End If

        End With

    Next Lrow2

End With



End Sub

Excel and really large amounts of data don't mix, it's not really meant to be a database :\ — Jesus Ramos, May 01 '13 at 21:21
That's a very wrong way of finding the last cell. See [THIS](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba) Also see [THIS](http://social.msdn.microsoft.com/Forums/en-US/exceldev/thread/da7d1600-43bc-4e04-b2cc-8c4871349964) Here I use a `delrange` object to delete relevant rows. Simply amend it to suit your needs. — Siddharth Rout, May 01 '13 at 21:41
Another Way. Use Autofilter to filter Col K on `0` and then delete the relevant rows. See [THIS](http://stackoverflow.com/questions/11631363/how-to-copy-a-line-in-excel-using-a-specific-word-and-pasting-to-another-excel-s) This shows how to work with the filtered range. Simply amend it to delete your range — Siddharth Rout, May 01 '13 at 21:43

Alistair Weir · Accepted Answer · 2013-05-02T10:32:15.457

Agree with Siddharth comment autofilter is way to go. This should be a lot quicker.

Option Explicit
Sub delrows()
    Dim ws As Worksheet
    Dim LR As Long
    Dim rng As Range, frng As Range

    Application.ScreenUpdating = False

    Set ws = Sheets("dataset")  '<-- Change this to name of your worksheet
    With ws
        LR = .Range("A" & Rows.Count).End(xlUp).Row
        .AutoFilterMode = False
        Set rng = .Range("A1:C" & LR) '<-- Assuming K is the last column
        rng.AutoFilter 3, "0" '<-- 11 referes to Column K
        Set frng = rng.Offset(1, 0).SpecialCells(xlCellTypeVisible) '<-- Don't delete the header
        frng.EntireRow.Delete
        .AutoFilterMode = False
    End With

    Application.ScreenUpdating = True
End Sub

Edit: I just cleaned ~20000 rows (3 columns) of data in ~5 seconds. Obviously it depends how many matches there are too.

VBA macro crashing Excel

1 Answers1