Would like to delete rows based on cell content

[cindywylie]

Good Morning All,

The attached sample spreadsheet is divided into two sections. The top section is regular sales orders and the bottom section is non regular sales orders. I'm currently using the code below to delete rows based on color of column A. I would like to modify the current code to delete rows based on column A for regular sales orders only and to delete non regular sales orders based on SO status in column Q. Any non regular sales order with a SO status in column Q of A or S should be deleted with the macro. Any help is appreciated.

Sub deleteRowOnCollor()
    Dim lLR, lC As Long
    Application.ScreenUpdating = False
    lLR = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row 'Determine last row
    For lC = lLR To 3 Step -1
        Sheets(1).Cells(lC, 1).Activate
        If ActiveCell.Interior.Color = 15773696 Then 'Used index color.
            Rows(lC).Delete Shift:=xlUp 'delete row
        End If
    Next
    Application.ScreenUpdating = True
End Sub

[AlphaFrog]

Sub delete_non_reg_sales_orders()
    Dim lLR As Long, lC As Long
    Application.ScreenUpdating = False
    With Sheets(1)
        lLR = .Range("A" & Rows.Count).End(xlUp).Row 'Determine last row
        For lC = lLR To 3 Step -1
            If UCase(.Range("Q" & lC).Value) = "A" Or UCase(.Range("Q" & lC).Value) = "S" Then
                .Rows(lC).Delete Shift:=xlUp 'delete row
            End If
        Next lC
    End With
    Application.ScreenUpdating = True
End Sub

[cindywylie]

AlphaFrog,

Thanks for the quick response. There is just one issue. I only want to delete rows in the bottom half of the sheet based on content in column Q. Your code deletes all rows based on column Q.

[AlphaFrog]

AlphaFrog,

Thanks for the quick response. There is just one issue. I only want to delete rows in the bottom half of the sheet based on content in column Q. Your code deletes all rows based on column Q.

This only deletes between the two "Non-Regular Sales Orders" headers in column A

Sub delete_non_reg_sales_orders()
    Dim lLR As Long, lC As Long, nrso As Integer
    Application.ScreenUpdating = False
    With Sheets(1)
        lLR = .Range("A" & Rows.Count).End(xlUp).Row 'Determine last row
        For lC = lLR To 3 Step -1
            If .Range("A" & lC).Value = "Non-Regular Sales Orders" Then nrso = nrso + 1
            If nrso = 1 Then
                If UCase(.Range("Q" & lC).Value) = "A" Or UCase(.Range("Q" & lC).Value) = "S" Then
                    .Rows(lC).Delete Shift:=xlUp 'delete row
                End If
            ElseIf nrso > 1 Then
                Exit For
            End If
        Next lC
    End With
    Application.ScreenUpdating = True
End Sub

[cindywylie]

AlphaFrog,

Perfect! Is there anyway to combine the macro for the top half and the macro you created. If I could have one macro for this sheet, that would be great. If thats too much I understand. Thanks for your time.

Sub deleteRowOnCollor()
    Dim lLR, lC As Long
    Application.ScreenUpdating = False
    lLR = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row 'Determine last row
    For lC = lLR To 3 Step -1
        Sheets(1).Cells(lC, 1).Activate
        If ActiveCell.Interior.Color = 15773696 Then 'Used index color.
            Rows(lC).Delete Shift:=xlUp 'delete row
        End If
    Next
    Application.ScreenUpdating = True
End Sub

Sub delete_non_reg_sales_orders()
    Dim lLR As Long, lC As Long, nrso As Integer
    Application.ScreenUpdating = False
    With Sheets(1)
        lLR = .Range("A" & Rows.Count).End(xlUp).Row 'Determine last row
        For lC = lLR To 3 Step -1
            If .Range("A" & lC).Value = "Non-Regular Sales Orders" Then nrso = nrso + 1
            If nrso = 2 Then Exit For
            If UCase(.Range("Q" & lC).Value) = "A" Or UCase(.Range("Q" & lC).Value) = "S" Then
                .Rows(lC).Delete Shift:=xlUp 'delete row
            End If
        Next lC
    End With
    Application.ScreenUpdating = True
End Sub

[AlphaFrog]

Sub delete_sales_orders()
    Dim lLR As Long, lC As Long, Section As String
    Application.ScreenUpdating = False
    With Sheets(1)
        lLR = .Range("A" & Rows.Count).End(xlUp).Row 'Determine last row
        For lC = lLR To 3 Step -1
            If .Range("A" & lC).Value Like "*Sales Orders" Then Section = .Range("A" & lC).Value
            Select Case Section
                Case "Regular Sales Orders"
                    If .Range("A" & lC).Interior.Color = 15773696 Then 'Used index color.
                        .Rows(lC).Delete Shift:=xlUp 'delete row
                    End If
                Case "Non-Regular Sales Orders"
                    If UCase(.Range("Q" & lC).Value) = "A" Or UCase(.Range("Q" & lC).Value) = "S" Then
                        .Rows(lC).Delete Shift:=xlUp 'delete row
                    End If
            End Select
        Next lC
    End With
    Application.ScreenUpdating = True
End Sub

[cindywylie]

Works perfectly. Thanks so much for your help!