Looking for Tips to Optimize VBA Code for Efficient Row Deletion

[Gunasekaran]

Hello,

I've written a VBA code for a project, and during manual testing, I observed that a specific area of the code is taking a long minutes\time to execute, with the cursor spinning for several minutes. I'm wondering if there's a way to optimize the code for faster row deletion. Any suggestions or advice would be greatly appreciated."

Total Number of Records for 20000 Records. even, my starting point, turn it off,

Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.ScreenUpdating = False


This version provides more context and makes it clearer what you're looking for, which can help forum members better understand your issue and provide more effective assistance.

First Code

    lr = wk.Cells(wk.Rows.Count, "A").End(xlUp).Row
    
    For i = lr To 2 Step -1 ' Start from the last row and go up to the second row
        If wk.Cells(i, "B").Value = "RESIGN PAYROLL" Then
            wk.Rows(i).Delete
        End If
    Next i

Second Code

  With wk
        lr = wk.Cells(wk.Rows.Count, "A").End(xlUp).Row
        .Columns("N").Insert Shift:=xlToRight
        .Range("N1").Value = "x"
        .Range("N2:N" & lr).FormulaR1C1 = "=INDEX('Site Details'!C[-12], MATCH(RC[-1], 'Site Details'!C[-13], 0))"
        
        On Error Resume Next
            .Range("A1:N" & lr).AutoFilter Field:=14, Criteria1:="<>" & Site, Operator:=xlFilterValues
        If Application.WorksheetFunction.Subtotal(103, .Range("N1:N" & lr)) > 1 Then
            .Range("N2:N" & lr).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        End If
        .AutoFilterMode = False
        On Error GoTo 0

        .Columns("N").Delete
End With


Thired code

   'Delete Grade lines except 6 to 8
    
    lr = wk.Cells(wk.Rows.Count, "J").End(xlUp).Row
    
    Dim filterRange As Range
    Set filterRange = wk.Range("N2:N" & lr)
    filterRange.AutoFilter Field:=1, Criteria1:="<25"
    
        If Application.WorksheetFunction.Subtotal(103, filterRange) > 1 Then
        
        wk.Rows("2:" & lr).SpecialCells(xlCellTypeVisible).Delete
    End If
    
    ' Turn off the filter
    wk.AutoFilterMode = False

 

[Gunasekaran]

Any update here?

 

[Marc L]

Hello, not complete code, not complete explanation, no attachment - just follow forum rules ! - this is just a guessing challenge​

for a mind readers forum rather than for any Excel forum so what could you expect ?!​

You can find on web threads about this common subject.​

To remind : sorting and clearing at once is faster than deleting …​

Another point : Excel can be 100 times slower than a database software with big data​

so this is not such a good idea to use Excel as a database software …​

​