Deleting rows based on the text in certain cells VBA

NoSparks · #5 02-03-2017, 10:32 AM

No wonder you let it run over night.

On my system, your code on that little dribble of data takes 1min 31 seconds leaving 16 rows.
Disabling screen updating reduces it to 4 seconds.

Using mickrickson's suggestion from post #4 at EF, adding your additional requirements, along with disabling screen updating takes less than 1 second.

Code:

Sub test()
    Dim i As Long
Application.ScreenUpdating = False
With ActiveSheet
For i = .Cells(Rows.Count, "C").End(xlUp).Row To 1 Step -1
    With .Rows(i)
        If .Range("P1").Value = "System Issues" Or .Range("P1").Value = "SME Floor Walker" _
                Or .Range("P1").Value = "Coaching" Or .Range("P1").Value = "Not Scheduled" _
                Or .Range("P1").Value = "Not Set Ready" Or .Range("P1").Value = "Available" _
                Or .Range("P1").Value = vbNullString Then
            If Not .Range("C1").Value Like "Agent:*" And Not .Range("C1").Value Like "Date:*" Then
                If Not .Range("M1").Value = "Break (Aux 2 - Agero Aux 4 - MG Break - Aux 71 HRB)" Then
                    .Delete
                End If
            End If
        End If
    End With
Next i
End With
Application.ScreenUpdating = True
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Conditional formating all cells in an array based on adjacent cells	deejay	Excel	6	12-20-2016 12:00 PM
Deleting rows with partially redundant data	Noah14	Excel Programming	1	09-19-2014 11:46 PM
Deleting rows & pushing them up causing issues	ep2002	Excel	2	08-29-2014 02:09 AM
Color-fill a range of cells, based on text in a different sheet. Possible?	unittwentyfive	Excel	2	06-01-2014 06:48 AM
Deleting rows with specific criteria	joflow21	Excel	9	11-22-2013 12:10 PM