Nested Loops Excel VBA

Excel VBA for non-programmers
Excel VBA for non-programmers

 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

Excel VBA 2013: For Non-Programmers

7 thoughts on “Nested Loops Excel VBA”

  1. 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

    1. (Yes, old comment, but possible future readers and learners)
      Because Y does not get incremented ( as in the Else statement), nor does it land on the next row as it would automatically happen when the row is deleted… so it keeps finding the same cell that it’s on… forever.
      If you don’t want to delete it, you need to make Y advance whether it finds a dupe or not, so:
      Sub FindDupeDelRow()
      ..’we start at row 2 if row 1 has only the headers
      ..X = 2
      ..Y = X + 1
      ..Do While Cells(X, 1).Value ””
      ….Do While Cells(Y, 1).Value ””
      ……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
      ……set rng=cells(y,1)
      ……End If
      ……Y = Y + 1
      ….Loop
      ….X = X + 1
      ….Y = X + 1
      ..Loop
      ..rng.select
      End Sub

  2. I have written the below code and getting a compile error as invalid next control variable reference ..can some one please tell me what is the mistake in this code
    Sub Test()
    Dim Eligible As Workbook
    Set Eligible = Workbooks.Open(“D:\Sandhya\VBA\Eligible.xlsx”)
    For i = 2 To 5
    n = Sheets(1).Cells(i, 2) And Sheets(1).Cells(i, 3)
    For j = 2 To 3
    p = Sheets(1).Cells(j, 1)
    For a = 2 To 5
    r = Sheets(1).Cells(a, 3)
    For b = 2 To 5
    s = Sheets(1).Cells(r, 3)
    ‘If n = p And r = s Then
    If ThisWorkbook.Sheets(1).Cells(i, 2) >= Eligible.Sheets(1).Cells(j, 1) And ThisWorkbook.Sheets(1).Cells(a, 3) >= Eligible.Sheets(1).Cells(r, 3) Then
    ThisWorkbook.Sheets(1).Cells(i, 4) = Eligible.Sheets(1).Cells(j, 3)
    Else
    ThisWorkbook.Sheets(1).Cells(i, 5) = Eligible.Sheets(1).Cells(j, 3)
    End If
    Next j
    Next i
    Next b
    Next a
    End Sub

    1. Hi,
      You have most probably figure it out or found the solution, but here it is for other readers:
      The order of the “For” statements is: i, j, a, b.
      The order of the “Next” statements is: j, i, b, a
      where it should be the inverse or backward order of the For statements: b, a, j, i
      So,
      For i
      For j
      For a
      For b
      Next b
      Next a
      Next j
      Next i

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.