VBA: Subroutine to replicate the "Remove Duplicates" command is crashing excel

I'm new to VBA and have been writing subroutines of my own to practice. I thought it would be an interesting exercise to try to replicate the "remove duplicates" command with some rudimentary code. To do this, I used a "do until" loop nested in another "do until" loop. It should work as follows:

First we determine the last row. After this, the routine checks if the value of the cell in the second line equals the value of line 1, deletes the entire row if it is, then moves to the next row. It stops when it gets to the last row.

Breaking out of this inner loop, it adds 1 to a variable (OuterLoopCheck) which determines the line where the inner loop will start, then restarts the proccess. The whole proccess ends when the value of OuterLoopCheck = Last row.

When I execute this macro, excel crashes. I'd like to know why it is taking so much proccesing power. The code is below.

Sub RemoveDuplicates()

Dim InnerLoopCheck As Long, OuterLoopCheck As Long, LastRow As Long

LastRow = Range("A400").End(xlDown).Row   'For simplicity, I go to the last row from line 
                                          '400
InnerLoopCheck = 1
OuterLoopCheck = 1
 
Do Until OuterLoopCheck = LastRow

    Do Until InnerLoopCheck = LastRow
    
        If Cells(InnerLoopCheck + 1, 1).Value = Cells(OuterLoopCheck, 1).Value Then
            Cells(InnerLoopCheck + 1, 1).Select
            Selection.EntireRow.Delete
            
            LastRow = LastRow - 1
            InnerLoopCheck = InnerLoopCheck - 1  ' If this statement is triggered, a 
                                                 'whole row is deleted,
                                                 ' moving the last row up by up position.
         End If
         
    
    InnerLoopCheck = InnerLoopCheck + 1
    Loop
    
    
OuterLoopCheck = OuterLoopCheck + 1
InnerLoopCheck = OuterLoopCheck
Loop

End Sub


from Recent Questions - Stack Overflow https://ift.tt/3qgJ6Au
https://ift.tt/eA8V8J

Comments

Popular posts from this blog

Spring Elasticsearch Operations

Network Error and Timeout on Authorize.net JS

Object oriented programming concepts (OOPs)