My Row delete code is soooooo slow......... need cleanup and/or modification please

[Javi]

Hi All,

The two codes below delete each EntireRow that has "Function carried out" in column I of its row. However, the sheet I am using this code on has approximately 40,000 lines occupied. Code 1 is a little faster than Code 2 but both of them are so slow there almost unusable.

Any help or assistance in speeding up this would be greatly appreciated.



Code 1

Sub SelRows_After1()
    Dim ocell As Range
    Dim rng As Range
    
    For Each ocell In Range("I3:I40000")
        
        If ocell.Value = "Function carried out" Then
            
            If rng Is Nothing Then
                
                Set rng = ocell.EntireRow
            Else
                
                Set rng = Union(rng, ocell.EntireRow)
            End If
        End If
    Next
    
     
    If Not rng Is Nothing Then rng.Delete
    
    Set rng = Nothing
    Set ocell = Nothing
End Sub

Code 2

Sub DelRow_FCO()




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




Dim DeleteRow As Long
Dim ws As Worksheet




Set ws = ActiveSheet


For DeleteRow = ws.Range("I" & Rows.Count).End(xlUp).Row To 3 Step -1


If ws.Range("I" & DeleteRow).Value = "Function carried out" Then
    Rows(DeleteRow).EntireRow.Delete
    End If
    
Next DeleteRow




Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = True




End Sub

 

[Javi]

Close all programs restart computer and new codes are running perfectly. Thanks all for your help.

Sometimes it's the most basic solution, restart your computer, I did not live by my own advice.

Thanks again!!!

 

[Fluff]

Glad we could help & thanks for the feedback