[Excel VBA] How to Delete Duplicate Rows Using a Loop (Sorted)

Introduction

When managing customer lists or product masters, you often encounter duplicate data registered across multiple rows. While Excel’s “Remove Duplicates” feature is powerful, you may want to incorporate this logic into a VBA macro.

This article explains the classic, reliable algorithm of sorting data first and then removing duplicate rows using a loop. Finally, I will also introduce the modern, simpler method using the built-in VBA function.

Basic Logic of the Process

The “Classic Method” consists of two main steps:

1. Sort: First, sort the entire data range based on the key column (e.g., Name or ID). This groups identical values next to each other.
2. Loop Backwards & Compare: Next, loop from the last row up to the first. Compare the current row’s value with the row above it. If they are the same, delete the current row.

Note: When deleting rows, you must always loop backwards. This prevents the index numbers of the remaining rows from shifting and causing errors or skipped checks.

VBA Code 1: The “Classic” Loop Method

Sub DeleteDuplicateRowsByLooping()

    ' Declare variables
    Dim dataArea As Range
    Dim keyColumn As Range
    Dim i As Long

    '--- Settings ---
    ' Target data range (Entire table including A1)
    Set dataArea = ActiveSheet.Range("A1").CurrentRegion
    ' Column to check for duplicates (1st column of the range)
    Set keyColumn = dataArea.Columns(1)
    '--- End Settings ---
    
    Application.ScreenUpdating = False

    '--- 1. Sort the entire range using the key column ---
    ' This groups duplicate values together
    dataArea.Sort Key1:=keyColumn, Order1:=xlAscending, Header:=xlYes

    '--- 2. Loop from the LAST row up to the 2nd row ---
    For i = dataArea.Rows.Count To 2 Step -1
        ' Compare the current row's value with the value of the row above it
        ' Using Columns(1) to check the key column
        If dataArea.Cells(i, 1).Value = dataArea.Cells(i - 1, 1).Value Then
            ' If they match, delete the entire current row
            dataArea.Rows(i).EntireRow.Delete
        End If
    Next i
    
    Application.ScreenUpdating = True
    
    MsgBox "Duplicate removal complete."

End Sub

Explanation of Code 1

1. Sorting (.Sort)VBAdataArea.Sort Key1:=keyColumn, Order1:=xlAscending, Header:=xlYes We use the .Sort method of the Range object.
   • Key1: The column used as the basis for sorting.
   • Order1: Sort order (xlAscending or xlDescending).
   • Header: Set to xlYes to exclude the header row from sorting.
2. Backwards Loop (Step -1)VBAFor i = dataArea.Rows.Count To 2 Step -1 The Step -1 instruction is crucial. It tells VBA to count downwards. Inside the loop, dataArea.Cells(i, 1) (current row) is compared with dataArea.Cells(i - 1, 1) (row above). If they match, EntireRow.Delete removes the duplicate.

VBA Code 2: The “Modern” Recommended Method

Modern Excel VBA provides a dedicated method called .RemoveDuplicates that performs this entire operation in a single line. This is faster and cleaner.

Sub DeleteDuplicates_EasyWay()
    Dim dataArea As Range
    Set dataArea = ActiveSheet.Range("A1").CurrentRegion
    
    ' Remove duplicates based on the 1st column (Header exists)
    dataArea.RemoveDuplicates Columns:=1, Header:=xlYes
    
    MsgBox "Duplicate removal complete. (RemoveDuplicates)"
End Sub

Summary

When removing duplicates in VBA, knowing the .RemoveDuplicates method significantly improves code quality. While the “Sort and Loop” method is excellent for understanding logic, the RemoveDuplicates method is strongly recommended for practical business use.