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

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