XL2003 VBA: While...Wend Loop Errors When Executed Twice (Run-time error '91')

[TechTank]

Hi All

I found the following code on the internet and have tried to use it more than once in a macro to delete rows that have the value "TRUE" in them. This works for the first instance of the code very successfully and the second until it errors on the second execution when no more "TRUE" cells are found:

 Option Explicit

Sub Delete_Rows_With_TRUE()

Dim SearchCriteria As String:   SearchCriteria = "TRUE"
Dim CriteriaRow As Long   

[B]### Rest of Code to import data is here but removed for easier reading ###[/B]

ActiveSheet.Range("A1").Activate
    On Error GoTo Next1
    CriteriaRow = Cells.Find(What:=SearchCriteria, After:=ActiveCell, LookIn:=xlFormulas, _
                    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=True, SearchFormat:=False).Row
    
    While CriteriaRow > 0
        ActiveSheet.Rows(CriteriaRow & ":" & CriteriaRow).Delete Shift:=xlUp
        On Error GoTo Next1
        CriteriaRow = Cells.Find(What:=SearchCriteria, After:=ActiveCell, LookIn:=xlFormulas, _
                        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                        MatchCase:=False, SearchFormat:=False).Row
    Wend
    GoTo Next1
    
Next1:

'Delete Obsolete Rows
Range("B:IV").Delete

When my macro runs through and gets to this second While...Wend loop it deletes the rows that have "TRUE" in them but then errors The code for the second While...Wend loops is the same as the first except the exit statements of 'Next2':

[B]### Rest of Code to import data is here but removed for easier reading ###[/B]

 ActiveSheet.Range("A1").Activate
    On Error GoTo Next2
    CriteriaRow = Cells.Find(What:=SearchCriteria, After:=ActiveCell, LookIn:=xlFormulas, _
                    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=True, SearchFormat:=False).Row
    
    While CriteriaRow > 0
        ActiveSheet.Rows(CriteriaRow & ":" & CriteriaRow).Delete Shift:=xlUp
        On Error GoTo Next2
        CriteriaRow = Cells.Find(What:=SearchCriteria, After:=ActiveCell, LookIn:=xlFormulas, _
                        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                        MatchCase:=False, SearchFormat:=False).Row
    Wend
    GoTo Next2

Next2:

'Delete Obsolete Rows
Range("C:IV").Delete

The error is:

Microsoft Visual Basic

Run-time error '91':

Object variable or With block variable not set

I'm quite new to VBA and would really appreciate a pointer or any help someone could give.

Thank you for reading and for your time.

Mark.

 

[shg]

Try this:

Sub Delete_Rows_With_TRUE()
    Dim rFind       As Range
 
    Set rFind = Cells.Find(What:=True, _
                           LookIn:=xlFormulas, _
                           LookAt:=xlWhole)
 
    Do While Not rFind Is Nothing
        rFind.EntireRow.Delete
        Set rFind = Cells.FindNext
    Loop
End Sub

 

[TechTank]

shg:

You're a genius! Works like an absolute charm! Thank you so much.

This forum rocks for helpful and nice people. Thank you all so so much.

Mark.

 

[shg]

You're welcome, glad it helped.