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
Breno Barbosa
  • 1
  • 2
  • Use `Union` and delete *after* looping, see [this](https://stackoverflow.com/questions/59975239/for-loop-not-fully-cycling-in-excel-vba) and similar. – BigBen Jan 11 '22 at 21:35
  • Use `LastRow = Range("A" & Rows.Count).End(xlUp).Row` or `LastRow = Cells(Rows.Count, "A").End(xlUp).Row`. – VBasic2008 Jan 12 '22 at 00:10

1 Answers1

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!

Breno Barbosa
  • 1
  • 2