How to Delete Duplicate Rows in Excel Using VBA (8 Methods)

Dataset Overview

To explain these methods, we will use a dataset that contains information about the department, working hours, and the salary of some employees. In our dataset, we have two sets of duplicate rows. Row 9 is a duplicate of Row 5 and Row 7 is a duplicate of Row 6.

Method 1 – Delete Identical Rows with Header in Excel using VBA

• Open your Excel workbook.
• Go to the Developer tab and select Visual Basic. The Visual Basic window will appear.

• Click Insert and choose Module.
• In the Module window, enter the following code:

Sub Delete_Duplicate_Rows_with_Headers()
Range("B4:E12").RemoveDuplicates Columns:=Array(1), Header:=xlYes
End Sub

Explanation

Here,

• • We have selected the range of our dataset using Range(“B4:E12”).
  • We used RemoveDuplicates and selected the column with Array(1).
  • Array(1) indicates the first column of our dataset. That means it indicates the Employee column.
  • Header:=xlYes denotes that the dataset has headers.

• Press Ctrl + S to save the code or click the Save icon.
• Go to the Developer tab and select Macros.

• A Macro window will open. Select your desired code and click Run.

• The duplicate rows (e.g., Mike and Sophie) will be deleted.

• To remove duplicates based on the second column (Department column), modify the code as follows:

Sub Delete_Duplicate_Rows_with_Headers()
Range("B4:E12").RemoveDuplicates Columns:=Array(2), Header:=xlYes
End Sub

• Save the code by pressing Ctrl + S.
• Press the F5 key to Run the code.

Read More: Excel VBA: Remove Duplicates from an Array

Method 2 – VBA to Remove Duplicate Rows without Header in Excel

• Open your Excel workbook.
• Go to the Developer tab and select Visual Basic.

• Click Insert and choose Module.
• In the Module window, enter the following code:

Sub Delete_Duplicate_Rows_without_Headers()
Range("B4:E12").RemoveDuplicates Columns:=Array(2), Header:=xlNo
End Sub

This code removes duplicate rows based on the second column (Department column) without considering headers.

• Press Ctrl + S to save the code.
• Go to the Developer tab and select Macros. A Macro window will appear.

• Select the desired code from the Macro window and click Run.

• The duplicates have been removed.

Method 3 – Apply VBA to Delete Similar Rows from an Excel Table

• Open your Excel workbook.
• Go to the Developer tab and select Visual Basic.

• Click Insert and choose Module.
• Enter the following code in the Module window:

Sub Delete_Duplicate_Rows_from_Table()
ActiveSheet.ListObjects("Table1").DataBodyRange.RemoveDuplicates Columns:=Array(1), _
Header:=xlYes
End Sub

Here, you have to write the table name inside ListObjects(). Our table name is Table1.

This code removes duplicate rows from an Excel table based on the first column (considering headers).

• Press Ctrl + S to save the VBA code.
• Select Macros from the Developer tab.

• Select the desired code from the Macro window and click Run.

• The duplicates have been removed.

Method 4 – Delete Duplicate Rows from a Column in Excel using VBA

To remove duplicate rows from a column, we will use the previous dataset again. However, we changed the value of Cell D11 to 7 from 6.  This is done to make the procedure easier to understand.

• Open your Excel workbook with the dataset.
• Go to the Developer tab and select Visual Basic. The Visual Basic window will appear.

• Click Insert and choose Module.
• In the Module window, enter the following code:

Sub Delete_Duplicate_Rows_from_a_Column()
Dim x As Range
Dim lstrw As Long
lstrw = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
Set x = ActiveSheet.Range("B4:E12" & lstrw)
x.RemoveDuplicates Columns:=2, Header:=xlYes
End Sub

This code removes duplicate rows based on the second column (Department column) while considering headers.

• Save the code, press Ctrl + S.
• Go to the Developer tab, select Macros, and run your desired code.

• The duplicate rows will be deleted, keeping only one occurrence.

Method 5 – VBA to Remove Identical Rows Keeping the Last Duplicate

In the previous methods, we deleted the last duplicate row and kept the first one. But in this method, we will keep the last duplicate row. To explain this method, we will use the previous dataset. However, we can’t use any merged cells at the beginning of our dataset. We therefore have deleted the dataset heading from the previous dataset. We have highlighted the duplicated rows below.

• Open your Excel workbook with the modified dataset (no merged cells at the beginning).
• Go to the Developer tab and select Visual Basic.

• Click Insert and choose Module.
• Enter the following code in the Module window:

Sub Delete_Identical_Rows_Keeping_the_Last_Duplicate()
Dim MyRng As Range
Set MyRng = Application.InputBox("Select the column having duplicates", Type:=8)
With CreateObject("scripting.dictionary")
For y = Cells(Rows.Count, MyRng.Column).End(xlUp).Row To 1 Step -1
If Not .exists(Cells(y, MyRng.Column).Value) Then
.Add Cells(y, MyRng.Column).Value, Nothing
Else
Rows(y).EntireRow.Delete
End If
Next
End With
End Sub

This code prompts the user to select the column where duplicates should be checked.  It keeps the last occurrence of each duplicate value.

• Press Ctrl + S to save the code.
• Go to the Developer tab, select Macros, and run your desired code.

• The Input window will appear.

• Select the column where you want to check duplicates. We have selected Column B.

• Click OK to see the results.

Method 6 – Delete Duplicate Rows Based on All Columns with Excel VBA

Here, just row 6 & row 7 are exactly the same. But row 5 and row 9 have a difference in the Hour/Day column. So, they are not exactly the same.

• Open your Excel workbook with the dataset.
• Go to the Developer tab and select Visual Basic.

• Click Insert and choose Module.
• Enter the following code in the Module window:

Sub Delete_Duplicate_Rows_Based_on_All_Columns()
Dim MyRng As Range
Dim MainColumns As Variant
Set MyRng = Range("B4:E12")
AllColumns = MyRng.Columns.Count
ReDim MainColumns(0 To AllColumns - 1)
For i = 0 To AllColumns - 1
MainColumns(i) = i + 1
Next i
MyRng.RemoveDuplicates Columns:=(MainColumns), Header:=xlYes
End Sub

This code removes duplicate rows based on all four columns, considering headers.

• Press Ctrl + S to save the code.
• Go to the Macros from the Developer tab.

• Select the desired code from the Macro window and Run it.

• You will see the following result:

Read More: Excel VBA: Remove Duplicates Comparing Multiple Columns

Method 7 – Remove Identical Rows Based on Specific Columns using VBA

• Open your Excel workbook with the dataset.
• Go to the Developer tab and select Visual Basic. The Visual Basic window will open.

• Click Insert and choose Module.
• In the Module window, enter the following code:

Sub Delete_Duplicate_Rows_Based_on_Specific_Columns()
Dim x As Range
Set x = Range("B4:E12")
x.RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
End Sub

This code removes duplicate rows based on columns 1 and 2 (Employee and Department columns) while considering headers.

• Press Ctrl + S to save the code.
• Select Macros from the Developer tab.

• Select the code and Run it from the Macro window.

• The duplicate rows will be deleted, keeping only one occurrence.

Method 8 – Remove Duplicate Rows in Excel after Sorting

In this last method, we will delete duplicate rows after sorting. We will use a dataset that contains the ID Number of the employees.

• Open your Excel workbook with the dataset containing employee ID numbers (unsorted).
• Go to the Developer tab and select Visual Basic.

• Click Insert and choose Module.
• Enter the following code in the Module window:

Sub Delete_Duplicate_Rows_after_Sorting()
Dim myrg As Range
Dim xmyrg As Range
Set myrg = Range("B4", Range("B4").End(xlDown).End(xlToRight))
Set xmyrg = Range("B4", Range("B4").End(xlDown))
myrg.Sort Key1:=xmyrg, Order1:=xlAscending, Header:=xlYes
Range("B4:E12").RemoveDuplicates Columns:=Array(2), Header:=xlYes
End Sub

This code sorts the dataset in ascending order based on the employee ID numbers (column 2) and then removes duplicate rows.

• Press Ctrl + S to save the code.
• Select Macros from the Developer tab.

• Select the desired code and Run it from the Macro window.

• The duplicates have been deleted.

Download Practice Book

You can download the practice workbook from here: