CODEWITHSUNDEEP
excelvba
user3874360
user3874360

Reputation: 3

Code runs well, but stuck in a loop

Let me preface by saying I am not highly knowledgeable in this context. I found some code online and tailored it to my needs. When I run it, it does what I want it to do (delete a row the contains one value in column K and one value in column J), but it will continually run without end. When I cancel it, it has completed the task at hand, so I assume it is just stuck in a loop. Any and all help would be great.

"If Not rFind Is Nothing Then" is the line that is highlighted when the code Debugs.

Sub DeleteRows()

    Sheets("Sheet1").Select
    Dim rFind As Range
    Dim rFind2 As Range
    Dim rDelete As Range
    Dim strSearch As String
    Dim iLookAt As Long
    Dim bMatchCase As Boolean

    strSearch = Range("AJ1")
    strSearch2 = Range("AK1")

    iLookAt = xlWhole
    bMatchCase = False

    Set rDelete = Nothing

    Application.ScreenUpdating = False
    With Sheet1.Columns("J:J")
        Set rFind2 = .Find(strSearch2, LookIn:=xlValues, LookAt:=iLookAt, SearchDirection:=xlPrevious, MatchCase:=bMatchCase)
        If Not rFind2 Is Nothing Then
            Do
                With Sheet1.Columns("K:K")
                    Set rFind = .Find(strSearch, LookIn:=xlValues, LookAt:=iLookAt, SearchDirection:=xlPrevious, MatchCase:=bMatchCase)
                    If Not rFind Is Nothing Then
                        Do
                            Set rDelete = rFind
                            Set rFind = .FindPrevious(rFind)
                            If rFind.Address = rDelete.Address Then Set rFind = Nothing
                            rDelete.EntireRow.Delete
                        Loop While Not rFind Is Nothing
                    End If
                End With
            Loop While Not rFind2 Is Nothing
        End If
    End With
    Application.ScreenUpdating = True
End Sub

Upvotes: 0

Views: 370

Answers (1)

Reg Edit
Reg Edit

Reputation: 6916

Your outer loop

Loop While Not rFind2 Is Nothing

will continue to loop, as nothing ever sets rFind2 to Nothing. It looks as though you want to clear it in the same place as rFind:

 If rFind.Address = rDelete.Address Then
     Set rFind = Nothing
     Set rFind2 = Nothing
 End If

Upvotes: 1

Related Questions