Deleting rows based on the text in certain cells VBA

ThisGuyJohn · #1 01-31-2017, 08:17 PM

Hello all this is my first post

So what I am trying to do is wright a macro to delete certain ROWS based on text in certain cells down COLUMN P for example look down COLUMN P and delete all rows with "Available"
BUT also looking down COLUMN C and M and saving ROWS with certain text in them
so save all ROWS in COLUMN C that have the text with "Agent:..." and "Date:..." and the text in COLUMN M with the specific text of "Break (Aux 2 - Agero Aux 4 - MG Break - Aux 71 HRB)"

so I can get excel to do all of this but look down COLUMN M if I take out all the code that has to do with COLUMN M everything works how I want it to. but when I add the code for COLUMN M I get this error
run time error 1004
application defined or object defined error
and excel highlights this row of code in yellow

Code:

For m = .Cells(Rows.Count, "M").End(x1up).Row To 1 Step -1 'COLUMN M

This is the full code

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 'COLUMN C
        For m = .Cells(Rows.Count, "M").End(x1up).Row To 1 Step -1 'COLUMN M
            With .Rows(c)
            With .Rows(m)
            
                'look at COLUMN P and delete ROWS with specific text
            
                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
                
                   'saves ROWS with text like "Agent:*" in COLUMN C
            
                    If Not .Range("C1").Value Like "Agent:*" Then
                    
               'look at COLUMN P and delete ROWS with specific text
            
                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
                
                    'saves ROWS with text like "Date:*" in COLUMN C
            
                    If Not .Range("C1").Value Like "Date:*" Then
                    
                'look at COLUMN P and delete ROWS with specific text
            
                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
                
                    'saves ROWS with the text "Break (Aux 2 - Agero Aux 4 - MG Break - Aux 71 HRB)" in COLUMN M
            
                    If Not .Range("M1").Value = "Break (Aux 2 - Agero Aux 4 - MG Break - Aux 71 HRB)" Then
                        'deletes ALL ROWS that were not saved
            
                        .Delete
                        
                    End If
                    End If
                    End If
                End If
                End If
                End If
            End With
            End With
        Next m
        Next c
    End With
    
End Sub

ThisGuyJohn · #2 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

NoSparks · #3 02-02-2017, 09:32 PM

Attach a sample sheet, personal info removed, so we have something to work with and I'm sure we can assist.

ThisGuyJohn · #4 02-02-2017, 09:47 PM

So real quick this is just 2 agents just to show what im looking at the space between each "Agent:..." is always different. everything is merged weird feel free to unmerge to see it (i never do even with the macro) all the "Agent:..." is always on COLUMN C as well as the "Date:..." the Scheduled Activity is COLUMN M

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

ThisGuyJohn · #6 02-03-2017, 03:16 PM

ya thanks this works quicker especially when I run a days worth.
Sometimes I have to run a whole pay period and swap out "Breaks" for "Coaching" or "Not Set Ready" or whatever and with so many people it takes forever the last report I ran had 35,000 rows I can't imagine waiting for it to finish during the day

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