Deleting rows based on color and text in 2 different columns

Robdiqulous

New Member
Joined
Sep 11, 2017
Messages
15
I am trying to delete a row if it HAS a red color in Column C AND if it DOESN'T have the words PO box in Column N. I have gotten it to where it deletes the rows that DO have po box in N but I can't seem to get it to go the other way and KEEP the rows with po box. Here is my code. The 13551615 is the red background color.
Also if there is a faster way to do this that would be great too. This takes about 13 seconds to run through my 3k rows. I only have like 20 rows with the color in them and I already sort those to the top. But I really just want this to work even if it takes a second.


Code:
    Dim lngRow As Long
    Dim lngRows As Long
    'Find the last row in Column A
    lngRows = Range("A" & Rows.Count).End(xlUp).Row
     
    For lngRow = lngRows To 2 Step -1
        If ActiveWorkbook.Worksheets("Sheet1").Cells(lngRow, "C").FormatConditions(1).Interior.Color = 13551615 Then
            If Not InStr(1, LCase(Range("N" & lngRow)), LCase("PO Box")) <> 0 Then
                ActiveWorkbook.Worksheets("Sheet1").Rows(lngRow).EntireRow.Delete
            End If
        End If
        
    Next
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
If anyone finds this helpful, I figured it out myself in the end. I filtered for the color, then I used the other code I had already. The code I was using would filter all of the PO boxes, but it wouldn't take into consideration the color. SO it was just deleting ALL of the rows that didn't have po box. This works perfectly and actually works a TON faster. in about 1-2 seconds.

Code:
Sub Filter_Colors()


    Dim UsdRws As Long
    
    ' This next line uses Column C then finds the last row and puts that as the modifier range for how many rows to filter
        UsdRws = Range("C" & Rows.Count).End(xlUp).Row
    'use A1:Z1 for the autofilter. I only need to use C to filter but this makes everything easier.
        Range("A1:Z1").autofilter
    ' This will now use the range from A2-Z also going down as far as Col C shows there is information (UsdRws)
    With Range("A2:Z" & UsdRws)
        .autofilter Field:=3, Criteria1:=RGB(255, 199, 206), Operator:=xlFilterCellColor
        On Error Resume Next
    End With
    
    Dim lngRow As Long
    Dim lngRows As Long
    'Find the last row in Column A
    lngRows = Range("C" & Rows.Count).End(xlUp).Row
     
    For lngRow = lngRows To 2 Step -1
        If (ActiveWorkbook.Worksheets("Sheet1").Cells(lngRow, "C").FormatConditions(1).Interior.Color = 13551615 _
            And (InStr(1, LCase(Range("N" & lngRow)), LCase("PO Box")))) Then
        Else
            ActiveWorkbook.Worksheets("Sheet1").Rows(lngRow).EntireRow.Delete
        End If
        
    Next
    Sheets("Sheet1").Select
        ActiveSheet.AutoFilterMode = False


End Sub
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,824
Messages
6,181,186
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top