Deleting Rows with cells that have certain contents, ran into a problem

[jkream]

I am modifying a borrowed snippet that has worked for me before. But since my understanding is limited, I am stepping through as I add 'variables' I am getting stuck on one and I cannot see why.
I have a sheet with about 16k rows. for some rows the person named has filed bankruptcy so I want to remove them. The cells denoting bankruptcy are in the Street address column "I" although I will be expanding this to J, and K as well
The problem is various users adding to the sheet have denoted bankruptcy differently. They may have entered CH 7, or CH7, or ch7, or Chap7, and so on, and the same for chapter 13 as well.

So I have the ch 7's working fine but seemingly randomly, because I can't find the reason and it does not seem specific to the line or the line content, it stops with Runtime error 424 Object Required

In the code below it is stopping on the line reading:
If .Value = "chap 13" Then

Sub DeleteCH7()

    Dim Firstrow As Long
    Dim Lastrow As Long
    Dim Lrow As Long
    Dim CalcMode As Long
    Dim ViewMode As Long

    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    'We use the ActiveSheet but you can replace this with
    'Sheets("MySheet")if you want
    With ActiveSheet

        'We select the sheet so we can change the window view
        .Select

        'If you are in Page Break Preview Or Page Layout view go
        'back to normal view, we do this for speed
        ViewMode = ActiveWindow.View
        ActiveWindow.View = xlNormalView

        'Turn off Page Breaks, we do this for speed
        .DisplayPageBreaks = False

        'Set the first and last row to loop through
        Firstrow = .UsedRange.Cells(1).Row
        Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

        'We loop from Lastrow to Firstrow (bottom to top)
        For Lrow = Lastrow To Firstrow Step -1

            'We check the values in the I column in this example also J, K
            With .Cells(Lrow, "I")

                If Not IsError(.Value) Then

                    If .Value = "ch7" Then .EntireRow.Delete
                    If .Value = "ch 7" Then .EntireRow.Delete
                    If .Value = "CH7" Then .EntireRow.Delete
                    If .Value = "CH 7" Then .EntireRow.Delete
                    If .Value = "CHAP 7" Then .EntireRow.Delete
                    If .Value = "chap 7" Then .EntireRow.Delete
                    If .Value = "CHAP7" Then .EntireRow.Delete
                    If .Value = "chap 7" Then .EntireRow.Delete
                    If .Value = "CH13" Then .EntireRow.Delete
                    If .Value = "CH 13" Then .EntireRow.Delete
                    If .Value = "CHAP 13" Then .EntireRow.Delete
                    If .Value = "chap 13" Then .EntireRow.Delete
                    'This will delete each row with the Value "ch7"
                    'in Column I, case sensitive.
                    
           End If

            End With

        Next Lrow

    End With

    ActiveWindow.View = ViewMode
    With Application
        .ScreenUpdating = True
        .Calculation = CalcMode
    End With

End Sub

Maybe there is a more efficient way to enter the "ch7" versions?
Your help is appreciated

 

[JLGWhiz]

I cannot duplicate the error, but the Object Required error message occurs when an object variable loses its value and the code attempts to use the varible. It is common when deleting rows to lose the value of the object. When the row goes away, so does the object value in some cases. I didn't find that here, but that is the type of thing to look for while debugging.

 

[jkream]

I cannot duplicate the error, but the Object Required error message occurs when an object variable loses its value and the code attempts to use the varible. It is common when deleting rows to lose the value of the object. When the row goes away, so does the object value in some cases. I didn't find that here, but that is the type of thing to look for while debugging.

Thanks,
Tthe generic "the Object Required error message occurs when an object variable loses its value and the code attempts to use the variable" answer is what I was finding online and didn't make much sense to me.

Your mention: "It is common when deleting rows to lose the value of the object. When the row goes away, so does the object value in some cases." Is illuminating, but I am not sure how to look for or correct for this since the point is to delete rows?

 

[JLGWhiz]

The loss of the object value due to deletion has occurred in using FindNext function for me. It happens when the variable is created as a found range and then when the row for that range is deleted, the variable value no longer exists and the FindNext function throws the error. I only use this as an illustration of how it can happen. You would need to analyze the condition of your code at the time the error occurs to determine why you are getting the message. Since i could not duplicate the error using the information furnished in the post, there is not much else I can tell you.

 

[Norie]

If you delete the entire row of the cell you are checking you delete the cell too, so the rest of the code here is going to fail as soon as you find a match.

For example if you find 'CH7' and delete the entire row all the following If statements will cause errors you can't get the value of a non-existent cell.

                    If .Value = "ch7" Then .EntireRow.Delete
                    If .Value = "ch 7" Then .EntireRow.Delete
                    If .Value = "CH7" Then .EntireRow.Delete
                    If .Value = "CH 7" Then .EntireRow.Delete
                    If .Value = "CHAP 7" Then .EntireRow.Delete
                    If .Value = "chap 7" Then .EntireRow.Delete
                    If .Value = "CHAP7" Then .EntireRow.Delete
                    If .Value = "chap 7" Then .EntireRow.Delete
                    If .Value = "CH13" Then .EntireRow.Delete
                    If .Value = "CH 13" Then .EntireRow.Delete
                    If .Value = "CHAP 13" Then .EntireRow.Delete
                    If .Value = "chap 13" Then .EntireRow.Delete

Try this instead.

Sub DeleteCH7()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long

    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    'We use the ActiveSheet but you can replace this with
    'Sheets("MySheet")if you want
    With ActiveSheet

    'We select the sheet so we can change the window view
        .Select

    'If you are in Page Break Preview Or Page Layout view go
    'back to normal view, we do this for speed
        ViewMode = ActiveWindow.View
        ActiveWindow.View = xlNormalView

    'Turn off Page Breaks, we do this for speed
        .DisplayPageBreaks = False

    'Set the first and last row to loop through
        Firstrow = .UsedRange.Cells(1).Row
        Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

    'We loop from Lastrow to Firstrow (bottom to top)
        For Lrow = Lastrow To Firstrow Step -1

    'We check the values in the I column in this example also J, K
            With .Cells(Lrow, "I")

                If Not IsError(.Value) Then
                    Select Case .Value
                        Case "ch7", "ch 7", "CH7", "CH 7", "CHAP 7", "chap 7", "CHAP7", "chap 7", _
                             "CH13", "CH 13", "CHAP 13", "chap 13"
                            .EntireRow.Delete
                        Case Else
                         ' do nothing
                    End Select

                End If

            End With

        Next Lrow

    End With

    ActiveWindow.View = ViewMode
    With Application
        .ScreenUpdating = True
        .Calculation = CalcMode
    End With

End Sub

 

[jkream]

Thank you. It turns out that it was looping and the next time it looped it was having the object error.
I got other help as well and this works:

Sub DeleteIJK()

    Dim Firstrow As Long
    Dim Lastrow As Long
    Dim Lrow As Long
    Dim CalcMode As Long
    Dim ViewMode As Long
    Dim nCurCol As Long

    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    'We use the ActiveSheet but you can replace this with
    'Sheets("MySheet")if you want
    With ActiveSheet

        'We select the sheet so we can change the window view
        .Select

        'If you are in Page Break Preview Or Page Layout view go
        'back to normal view, we do this for speed
        ViewMode = ActiveWindow.View
        ActiveWindow.View = xlNormalView

        'Turn off Page Breaks, we do this for speed
        .DisplayPageBreaks = False

        'For Next Loop to process columns, I, J, and K (referred to by number)
        For nCurCol = 9 To 11
        
            'Set the first and last row to loop through
            Firstrow = .UsedRange.Cells(1).Row
            Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
    
            'We loop from Lastrow to Firstrow (bottom to top)
            For Lrow = Lastrow To Firstrow Step -1
    
                'We check the values in the I column in this example also J, K
                With .Cells(Lrow, nCurCol) '// use numbers to refer to columns instead of letters - I is 9, J is 10, and K is 11
                    If Not IsError(.Value) Then
    
                        Select Case (UCase(.Value)) '// the UCASE function temporarily converts the value to upper case so you don't have to check variations
                            
                            Case "CH7", "CH 7", "CHAP7", "CHAP 7", "CH13", "CH 13", "CHAP13", "CHAP 13", "CH11", "CH 11", "CHAP11", "CHAP 11", "BKCY", "ACTIVE MILITARY", "DECEASED"
                                .EntireRow.Delete
                                'This will delete each row with a Value for variations on bankruptcy, military or deceased so there are no repeats in billing
                                'in Column I, case sensitive.
                        End Select
                   End If
                End With
    
            Next Lrow
        Next nCurCol

    End With
    ActiveWindow.View = ViewMode
    With Application
        .ScreenUpdating = True
        .Calculation = CalcMode
    End With

    '// nice to know when the macro finishes - delete next line if you don't want this
    MsgBox "done"
End Sub