Deleting rows based on the text in certain cells VBA

ThisGuyJohn · #1 02-02-2017, 06:40 PM

OK so I figured it out and got it to work
is there any way of simplifying it tho there is 4 if statements and usually about 10,000 to 30,000 rows in my report I run at work and i have to let it run overnight lol
I have tried combining all the IF NOT statements but excel only does whatever is listed first

combined IF NOT statements using ''or" or "and"

Code:

If Not .Range("C1").Value Like "Agent:*" or .Range("C1").Value Like "Date:*" or .Range("M1").Value = "Break (Aux 2 - Agero Aux 4 - MG Break - Aux 71 HRB)" Then

Code:

If Not .Range("C1").Value Like "Agent:*" and .Range("C1").Value Like "Date:*" and .Range("M1").Value = "Break (Aux 2 - Agero Aux 4 - MG Break - Aux 71 HRB)" Then

this is the full code that works

Code:

Sub Filter()
    Dim c As Long
    Dim m As Long
    With ActiveSheet
        For c = .Cells(Rows.Count, "C").End(xlUp).Row To 1 Step -1
        For m = .Cells(Rows.Count, "M").End(xlUp).Row To 1 Step -1
            With .Rows(c)
            With .Rows(m)
                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:*" Then
                    If 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 If
            End With
            End With
            
        Next m
        Next c
    End With
    
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