Trying to Delete row

[Eric Penfold]

This code all works but for some weird reason it won`t delete the row??

Option Explicit
Public intDeleteNonBO As Integer
Sub DeleteAnyNonBO()

    Dim wb     As Workbook
    Dim ws     As Worksheet
    Dim Rng    As Range, Cell As String, Col As String
    Dim LRow   As Long
    Dim cells  As Range
    Dim strText As String
    Dim i      As Integer

    intDeleteNonBO = 0
    
    With Application
        .ScreenUpdating = False
        .Calculation = xlManual
        .DisplayAlerts = False
        .EnableEvents = False
    End With
    
    Set wb = Workbooks("2023BackOrderReport.xlsm")
    Set ws = wb.Worksheets("Data")
    LRow = ws.cells(Rows.Count, 1).End(xlUp).Row
    Set Rng = ws.Range("I2:I" & LRow)
    
    On Error Resume Next
    
    For i = LRow To 2 Step -1
        Col = "A"
        Cell = ws.cells(i, 9).Value
        If IsDate(ws.cells(i, Col).Value) And ws.cells(i, Col).Value = Date Then
            If Cell Like "No Space" Then
                cells(i, "A").EntireRow.Delete
            ElseIf Cell Like "*Fit on Lorry" Then
                cells(i, "A").EntireRow.Delete
            ElseIf Cell Like "*FIT ON TRUCK" Then
                cells(i, "A").EntireRow.Delete
            ElseIf Cell Like "Failed Delivery" Then
                cells(i, "A").EntireRow.Delete
            ElseIf Cell Like "Bag Not]" Then
                cells(i, "A").EntireRow.Delete
            ElseIf Cell Like "Loading Picking Error" Then
                cells(i, "A").EntireRow.Delete
            ElseIf Cell Like "Failed Delivery" Then
                cells(i, "A").EntireRow.Delete
            ElseIf Cell Like "No Room on truck" Then
                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

 

[Muhammad_Usman]

If your VBA macro is not deleting rows as expected, there could be several reasons for this issue. Here are some common troubleshooting steps:

1. Check the Worksheet Name: Ensure that the worksheet with the name "Data" exists in the workbook "2023BackOrderReport.xlsm." Verify the spelling and case sensitivity of the worksheet name.

2. Verify the Criteria: Double-check the criteria in your code. Make sure that the conditions used to identify rows for deletion match the data in column A accurately. Any mismatch or typo in the criteria could prevent the correct rows from being deleted.

3. Data Format: Ensure that the data format in column A matches the criteria you are checking against. For example, if you are checking for "No Space," ensure that it appears exactly as "No Space" in the cells.

4. Data Cleaning: Sometimes, there may be extra spaces or characters in the cell values that are not immediately visible. You can use functions like `TRIM` to clean up the text before comparing it to the criteria.

5. Debugging: Add some debugging statements to your code to see what values are being compared and whether the conditions are being met. For example, you can add `Debug.Print` statements to print values to the Immediate window for inspection.

6. Error Handling: Ensure that there are no runtime errors occurring in your code. If an error occurs, it might disrupt the deletion process.

If the issue persists after checking these points, please provide more details about the specific problem or any error messages you are encountering, and I'll be happy to assist you further.

 

[Georgiboy]

From what i could see:
no need to dim 'cells' as a range.
'cells(i, "A")' should have been 'ws.cells(i, "A")'
the On Error part will prevent you from seeing what is going on in the code
when working with 'Like' i like to use UCASEor LCASE to force the text to be one or the other

Give it a try as below:

Option Explicit

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

    intDeleteNonBO = 0
   
    With Application
        .ScreenUpdating = False
        .Calculation = xlManual
        .DisplayAlerts = False
        .EnableEvents = False
    End With
   
    Set wb = Workbooks("2023BackOrderReport.xlsm")
    Set ws = wb.Worksheets("Data")
    LRow = ws.cells(Rows.Count, 1).End(xlUp).Row
   
    For i = LRow To 2 Step -1
        Col = "A"
        Cell = LCase(ws.cells(i, 9).Value)
        If IsDate(ws.cells(i, Col).Value) And ws.cells(i, Col).Value = Date Then
            If Cell Like "no space" Then
                ws.cells(i, "A").EntireRow.Delete
            ElseIf Cell Like "*fit on lorry" Then
                ws.cells(i, "A").EntireRow.Delete
            ElseIf Cell Like "*fit on truck" Then
                ws.cells(i, "A").EntireRow.Delete
            ElseIf Cell Like "failed delivery" Then
                ws.cells(i, "A").EntireRow.Delete
            ElseIf Cell Like "bag not]" Then
                ws.cells(i, "A").EntireRow.Delete
            ElseIf Cell Like "loading picking error" Then
                ws.cells(i, "A").EntireRow.Delete
            ElseIf LCase(Cell) Like "no room on truck" 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

 

[Eric Penfold]

Thanks works fine now.

 

[Georgiboy]

You're welcome, thanks for the feedback.