Nested Loops Excel VBA

Using nested loops in macros in Excel to delete duplicates: When we enter data in Excel spreadsheets, we can make a mistake. Now if our worksheet is large and we wish to delete duplicates, you can either filter the data first and then delete the duplicates which can be tedious process or you can create a macro using a ‘do while’ loop. Since the method involves a comparison between the data and then checking whether the two data are the same based on precisely defined conditions, we need two ‘do while’ loops – an outer and an inner. The outer loops identifies an item or data. The inner loop then compares the data with the data identified by the outer loop. When both are equal or same, we program the Excel macro to delete the data found by the inner loop. In short, the inside loop finds any rows that duplicate the starting row, and removes them. The outside loop moves the starting row down the selection, one row at a time, until each row has been compared with all those below it in the selection. The power of this macro is that you can adapt it to act on duplicate values in other columns in your data. Instead of deleting duplicate rows, you may like to highlight them! Note: Take care when running a macro! It will delete data from your spreadsheet. And when you run a macro in Excel, there is no Undo. What’s done is done. To be safe, copy your data first, and run this macro on the copy to test the results.

Macro Code with interesting explanation of the process:

Sub deleteduplicates()

‘using nested do while loops

‘we start at row 2 because row1 has only the headers

x=2

y=x+1

‘Just imagine placing a finger of the left hand on cell A2: Outer Loop – left finger

Do While Cells(x,1).Value<>””

‘Now imagine with a finger of the right-hand going slowly down each of the next rows starting from A3

‘Your right-hand finger movement will stop when you encounter a blank cell in column A

‘Start of inner loop – right finger

Do While Cells(y,1).Value<>””

‘compare the data shown by the left and right fingers, for example A2 and A3, B2 and B3

If Cells(x,1).Value=Cells(y,1).Value And Cells(x,2)=Cells(y,2).Value And Cells(x,3) = Cells(y,3) Then ‘delete if duplicate

Cells(y,1).EntireRow.Delete

Else

‘go down with your right-hand finger and compare again with data of left-finger

y=y+1

End If

Loop

‘here you have encountered a blank cell with your right hand finger. Now your left-hand finger goes to the next cell A3 and your right-hand finger goes to one cell below it.

x=x+1

y=x+1

‘ the same job of moving down with the right finger, comparing with the left finger and deleting if required

Loop

End Sub

Further reading: Get in the loop with Excel macros

One thought on “Nested Loops Excel VBA

  1. Jane

    hi,

    i have a question. if we relace the code ‘Cells(y,1).EntireRow.Delete’ with another code eg set rng=cells(y,1) then in the end rng.select
    i found this will lead to endless loop.

    can you please advise why?

    thanks

    Jane

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *