CODEWITHSUNDEEP
excelcrashexcel-2010vba
Oliver Burdekin
Oliver Burdekin

Reputation: 1108

VBA macro crashing Excel

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

Upvotes: 1

Views: 2581

Answers (1)

Alistair Weir
Alistair Weir

Reputation: 1849

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.

Upvotes: 1

Related Questions