Add an "Unless" statement to this code

[VBAProIWish]

Hello All,
I have this awesome macro here that deletes any row that has "Red" in the color column...

Sub Delete_Rows_with_X_in_Color_Column()
Dim cell_to_check As Variant
Dim range_to_check As Range
Dim nCol As Long
Dim rCount As Long
With ActiveSheet
    nCol = Application.Match("Color", .Rows(1), 0)
    For rCount = .UsedRange.Rows.Count To 2 Step -1
        Select Case .Cells(rCount, nCol).Value
            Case "Red", "red"
                .Rows(rCount).EntireRow.Delete
        End Select
    Next
End With

But now I need an exception to it (if that's the right terminology)
I would like the same code above to apply, EXCEPT IF/UNLESS the "Vehicle" column says "Taxi", then, do nothing.



Here's a before and after illustration...


<TABLE style="WIDTH: 481pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=641 border=0><COLGROUP><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 3401" width=93><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3913" width=107><COL style="WIDTH: 79pt; mso-width-source: userset; mso-width-alt: 3840" width=105><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1133" width=31><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 3401" width=93><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3913" width=107><COL style="WIDTH: 79pt; mso-width-source: userset; mso-width-alt: 3840" width=105><TBODY><TR style="HEIGHT: 24.75pt; mso-height-source: userset" height=33><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 229pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 24.75pt; BACKGROUND-COLOR: white; mso-ignore: colspan" align=middle width=305 colSpan=3 height=33>BEFORE MACRO</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 23pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=31></TD><TD class=xl66 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 229pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white; mso-ignore: colspan" align=middle width=305 colSpan=3>AFTER MACRO</TD></TR><TR style="HEIGHT: 24.75pt; mso-height-source: userset" height=33><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 24.75pt; BACKGROUND-COLOR: yellow" height=33>Customer</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow">Color</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow">Vehicle</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow">Customer</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow">Color</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow">Vehicle</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>ABC001</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Red</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Car</TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white"></TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">ABC002</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Blue</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Boat</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>ABC002</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Blue</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Boat</TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white"></TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">ABC003</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Green</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Car</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>ABC003</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Green</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Car</TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white"></TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">ABC004</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Red</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Taxi</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>ABC004</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Red</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Taxi</TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white"></TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">ABC006</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Orange</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Wagon</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>ABC005</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Red</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">SUV</TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white"></TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>ABC006</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Orange</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Wagon</TD><TD class=xl70 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: white"></TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD></TR></TBODY></TABLE>


You can see that there are 3 rows with "red" in the "Color" column, but in the "after" macro, only 2 rows were deleted. This is because while "Red" was indeed in the "Color" column, "Taxi" was in the "Vehicle" column and therefore the row was not deleted.


Hopefully this is an easy modification for you pros out there?

Thanks much!

 

[Ruddles]

Maybe:-

Option Compare Text
Sub Delete_Rows_with_X_in_Color_Column()
Dim cell_to_check As Variant
Dim range_to_check As Range
Dim nCol As Long
Dim rCount As Long
With ActiveSheet
    nCol = Application.Match("Color", .Rows(1), 0)
    For rCount = .UsedRange.Rows.Count To 2 Step -1
        Select Case .Cells(rCount, nCol).Value 
            Case "Red"
                If .Cells(rCount, nCol).Offset(0,1).Value <> "Taxi" Then .Rows(rCount).EntireRow.Delete
        End Select
    Next
End With

 

[HalfAce]

Hi Pro,
Try replacing this part of your code:

Case "Red", "red"
                .Rows(rCount).EntireRow.Delete

with something like this:

Case "Red", "red"
            If Not UCase(.Cells(rCount, nCol).Offset(, 1)) = "TAXI" Then .Rows(rCount).EntireRow.Delete

Hope it helps.

 

[Jonmo1]

Try

Sub Delete_Rows_with_X_in_Color_Column()
Dim cell_to_check As Variant
Dim range_to_check As Range
Dim nCol As Long
Dim rCount As Long
With ActiveSheet
    nCol = Application.Match("Color", .Rows(1), 0)
    For rCount = .UsedRange.Rows.Count To 2 Step -1
        Select Case UCase(.Cells(rCount, nCol + 1).Value)
            Case "TAXI"
                'Do Nothing
            Case Else
                Select Case UCase(.Cells(rCount, nCol).Value)
                    Case "RED"
                        .Rows(rCount).EntireRow.Delete
                End Select
        End Select
    Next
End With
End Sub

 

[VBAProIWish]

Thanks for the help guys. I guess I should've mentioned that the "Color" and "Vehicle" columns will NOT always be in the same position and will not always be in the same position next to each other. This is why the macro currently looks for the column named "Color" and not "Column B".

Is there a way to modify the code so that it looks for the vehicle column by name ("Vehicle") and by column position?

Thanks

 

[Jonmo1]

use the same match function you used to find the color column...

Sub Delete_Rows_with_X_in_Color_Column()
Dim cell_to_check As Variant
Dim range_to_check As Range
Dim nCol As Long
Dim nCol2 As Long
Dim rCount As Long
With ActiveSheet
    nCol = Application.Match("Color", .Rows(1), 0)
    nCol2 = Application.Match("Vehicle", .Rows(1), 0)
    For rCount = .UsedRange.Rows.Count To 2 Step -1
        Select Case UCase(.Cells(rCount, nCol2).Value)
            Case "TAXI"
                'Do Nothing
            Case Else
                Select Case UCase(.Cells(rCount, nCol).Value)
                    Case "RED"
                        .Rows(rCount).EntireRow.Delete
                End Select
        End Select
    Next
End With
End Sub

 

[Ruddles]

Typical user!

I'm not sure what the issue is - you've done it once with "Color", just repeat for "Vehicle":-

Option Compare Text
Sub Delete_Rows_with_X_in_Color_Column()
Dim cell_to_check As Variant
Dim range_to_check As Range
Dim nCol As Long
Dim vCol As Long
Dim rCount As Long
With ActiveSheet
    nCol = Application.Match("Color", .Rows(1), 0)
    vCol = Application.Match("Vehicle", .Rows(1), 0)
    For rCount = .UsedRange.Rows.Count To 2 Step -1
        Select Case .Cells(rCount, nCol).Value 
            Case "Red"
                If .Cells(rCount, vCol).Value <> "Taxi" Then .Rows(rCount).EntireRow.Delete
        End Select
    Next
End With

 

[Ruddles]

use the same match function you used to find the color column...

    nCol = Application.Match("Color", .Rows(1), 0)
    nCol2 = Application.Match("Vehicle", .Rows(1), 0)

Oops!

 

[Jonmo1]

Oops!

Thanks... Edited already..

 

[VBAProIWish]

LOL!

I guess I deserve to be picked on a little bit there


See, us newbies can't think that fast like you pros do.

That's why my name is VBAProIWish

Yes, in hindsight that appears ridiculously apparent now.

Tested and works great!

Thanks guys!