Free Microsoft Excel Training Videos

# Using Nested Loops in Macros in Excel

 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 comparision 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 colum A
'Start of inner loop - right finger
Do While Cells(y,1).Value<>""
'compare the data shown by the left and right fingers, for eample 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 leftt-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