0

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
2
  • Use Union and delete after looping, see this and similar. Commented Jan 11, 2022 at 21:35
  • Use LastRow = Range("A" & Rows.Count).End(xlUp).Row or LastRow = Cells(Rows.Count, "A").End(xlUp).Row. Commented Jan 12, 2022 at 0:10

1 Answer 1

0

Someone gave the correct answer but then deleted the post for some reason.

If anyone is interested, I made a stupid mistake by writing LastRow = Range("A400").End(xlDown).Row

when I meant to write LastRow = Range("A400").End(xlUp).Row.

This set the last row to 1048576, making excel do a gazillion iterations and crashing it.

My thanks to the person who pointed this out here!

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.