Delete Row if

[Eric Penfold]

Please help below trying to say if 2 cells in same row = specific values then delete row.

ElseIf (CellI) Like "* + collection*" And (CellJ) Like "*askwh*" Then

Option Explicit

Public intDeleteNonBO As Integer
Sub DeleteAnyNonBO()
    Dim wb     As Workbook
    Dim ws     As Worksheet
    Dim CellI   As String, Col As String, CellJ As String
    Dim LRow   As Long
    Dim i      As Integer, x As Integer

    intDeleteNonBO = 0
   
    With Application
        .ScreenUpdating = False
        .Calculation = xlManual
        .DisplayAlerts = False
        .EnableEvents = False
    End With
   
    Set wb = Workbooks("2024BackOrderReport.xlsm")
    Set ws = wb.Worksheets("Data")
    LRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
   
    For i = LRow To 2 Step -1
        On Error Resume Next
        Col = "A"
        CellI = LCase(ws.Cells(i, 9).Value)
        CellJ = LCase(ws.Cells(i, 10).Value)
        If IsDate(ws.Cells(i, Col).Value) And ws.Cells(i, Col).Value = Date Then
            If CellI Like "no space" Then
                ws.Cells(i, "A").EntireRow.Delete
            ElseIf CellI Like "*fit on*" Then
                ws.Cells(i, "A").EntireRow.Delete
            ElseIf CellI Like "*bag not*" Then
                ws.Cells(i, "A").EntireRow.Delete
            ElseIf CellI Like "*loading picking error*" Then
                ws.Cells(i, "A").EntireRow.Delete
            ElseIf (CellI) Like "*no room*" Then
                ws.Cells(i, "A").EntireRow.Delete
            ElseIf (CellI) Like "*no more room*" Then
                ws.Cells(i, "A").EntireRow.Delete
            ElseIf (CellI) Like "*error*" Then
                ws.Cells(i, "A").EntireRow.Delete
            ElseIf (CellI) Like "*failed*" Then
                ws.Cells(i, "A").EntireRow.Delete
            ElseIf CellI Like "*Not Picked*" Then
                ws.Cells(i, "A").EntireRow.Delete
            ElseIf CellI Like "*Not Loaded*" Then
                ws.Cells(i, "A").EntireRow.Delete
            ElseIf (CellI) Like "*no space*" Then
                ws.Cells(i, "A").EntireRow.Delete
            ElseIf (CellI) Like "*wouldn`t fit*" Then
                ws.Cells(i, "A").EntireRow.Delete
            ElseIf (CellI) Like "*wrong site*" Then
                ws.Cells(i, "A").EntireRow.Delete
            ElseIf (CellI) Like "*driver forgot*" Then
                ws.Cells(i, "A").EntireRow.Delete
            ElseIf (CellI) Like "*driver didn`t*" Then
                ws.Cells(i, "A").EntireRow.Delete
            ElseIf (CellI) Like "*Would not fit on Truck*" Then
                ws.Cells(i, "A").EntireRow.Delete
            ElseIf (CellI) Like "*Vehicle at capacity*" Then
                ws.Cells(i, "A").EntireRow.Delete
            ElseIf (CellI) Like "* & collection*" And (CellJ) Like "*askwh*" Then
                ws.Cells(i, "A").EntireRow.Delete
            ElseIf (CellJ) Like "*fr*" Then
                ws.Cells(i, "A").EntireRow.Delete
            End If
        End If
    Next i
   
    intDeleteNonBO = 1
   
    With Application
        .ScreenUpdating = True
        .Calculation = xlAutomatic
        .DisplayAlerts = True
        .EnableEvents = True
    End With
End Sub

 

[Alex Blakenburg]

You don't tell us what its is doing or not doing.
"Like" is case sensitive and you have lower-cased CellI & CellJ variables but some of your comparatives are mixed case ie "Not Picked" and "Not Loaded" etc.
It will only even get to those If statements if Column A contains a date and that date = Today, is that actually the case ? If you F8 through the code does it meet that test ?

 

[kvsrinivasamurthy]

Replace this portion by this code.

  If CellI Like "no space" Then
                ws.Cells(i, "A").EntireRow.Delete
            ElseIf CellI Like "*fit on*" Then
                ws.Cells(i, "A").EntireRow.Delete
            ElseIf CellI Like "*bag not*" Then
                ws.Cells(i, "A").EntireRow.Delete
            ElseIf CellI Like "*loading picking error*" Then
                ws.Cells(i, "A").EntireRow.Delete
            ElseIf (CellI) Like "*no room*" Then
                ws.Cells(i, "A").EntireRow.Delete
            ElseIf (CellI) Like "*no more room*" Then
                ws.Cells(i, "A").EntireRow.Delete
            ElseIf (CellI) Like "*error*" Then
                ws.Cells(i, "A").EntireRow.Delete
            ElseIf (CellI) Like "*failed*" Then
                ws.Cells(i, "A").EntireRow.Delete
            ElseIf CellI Like "*Not Picked*" Then
                ws.Cells(i, "A").EntireRow.Delete
            ElseIf CellI Like "*Not Loaded*" Then
                ws.Cells(i, "A").EntireRow.Delete
            ElseIf (CellI) Like "*no space*" Then
                ws.Cells(i, "A").EntireRow.Delete
            ElseIf (CellI) Like "*wouldn`t fit*" Then
                ws.Cells(i, "A").EntireRow.Delete
            ElseIf (CellI) Like "*wrong site*" Then
                ws.Cells(i, "A").EntireRow.Delete
            ElseIf (CellI) Like "*driver forgot*" Then
                ws.Cells(i, "A").EntireRow.Delete
            ElseIf (CellI) Like "*driver didn`t*" Then
                ws.Cells(i, "A").EntireRow.Delete
            ElseIf (CellI) Like "*Would not fit on Truck*" Then
                ws.Cells(i, "A").EntireRow.Delete
            ElseIf (CellI) Like "*Vehicle at capacity*" Then
                ws.Cells(i, "A").EntireRow.Delete
            ElseIf (CellI) Like "* & collection*" And (CellJ) Like "*askwh*" Then
                ws.Cells(i, "A").EntireRow.Delete
            ElseIf (CellJ) Like "*fr*" Then
                ws.Cells(i, "A").EntireRow.Delete
            End If

I have example here. Create a Array as given below with all elements in the macro . Use this code. I have added few elements. Add all required elements.

Ary = Array("no space", "fit on", "bag not", "loading picking error", "no room", "no more room", "error", "failed")
For Each S In Ary
If InStr(1, cellI, S) > 0 Then
ws.Cells(i, "A").EntireRow.Delete
Exit For
End If
Next

 

[Eric Penfold]

You don't tell us what its is doing or not doing.
"Like" is case sensitive and you have lower-cased CellI & CellJ variables but some of your comparatives are mixed case ie "Not Picked" and "Not Loaded" etc.
It will only even get to those If statements if Column A contains a date and that date = Today, is that actually the case ? If you F8 through the code does it meet that test ?

On the VBA watch both cells match what I have specified but the code skips over it so the row does not delete?

 

[Alex Blakenburg]

Can you clarify which line is actually failing.
If it is the date line, does Column A contain Date & Time ?
If so try using:

        If IsDate(ws.Cells(i, Col).Value) And Int(ws.Cells(i, Col).Value2) = Date Then

If it's one of the other conditions tell us which one and show us the values in CellI & CellJ when it fails.
If it's a case mismatch issue then @kvsrinivasamurthy's suggested approach may well solve it for you.

Note in post 1 you have a discrepancy:
One contains "+" the other "&"
Which would be in CellI ?
ElseIf (CellI) Like "* + collection*" And (CellJ) Like "*askwh*" Then
ElseIf (CellI) Like "* & collection*" And (CellJ) Like "*askwh*" Then

​

 

[kvsrinivasamurthy]

Have you seen Post #3.

 

[Eric Penfold]

Have you seen Post #3.

Yes thanks i will use your method looks way better than what i had.