How to Delete Duplicate Rows in Excel with VBA (8 Effective Ways)

In this article, we will learn to delete duplicate rows in Excel with VBA. VBA gives us the opportunity to perform any tasks according to the conditions. Sometimes, when we download or copy a dataset in our workbook, there can be many duplicate rows. These duplicate rows can produce errors or problems for performing different tasks. So, we need to delete these duplicate rows sometimes. Today, we will try to delete duplicate rows in Excel using VBA.

Delete Duplicate Rows in Excel with VBA: 8 Ways

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 will have two sets of duplicate rows. Row 9 is a duplicate of Row 5 here. And Row 7 is a duplicate of Row 6.

1. Delete Identical Rows with Header in Excel with VBA

In this first method, we will try to delete the identical rows with VBA. We have highlighted the duplicate rows below.

Let’s follow the steps below to learn this method.

STEPS:

• Firstly, go to the Developer tab and select Visual Basic. The Visual Basic window will appear.

• Secondly, go to Insert and select Module.
• Thirdly, type the code in the Module window:

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

Here, we have selected the range of our dataset using Range(“B4:E12”) as our dataset is from Cell B4 to E12. Then we used RemoveDuplicates and selected the column with the Array(). Array(1) indicates the first column of our dataset. That means it indicates the Employee column. Header:=xlYes denotes that the dataset has headers.

• Now, press Ctrl + S to save the code or click the Save icon.
• Next, go to the Developer tab and select Macros from there.

• A Macro window will occur. Select your desired code and hit Run.

• After that, you will see results like below.

Here, the VBA code deletes the duplicate rows of Mike and Sophie.

• Again, to remove duplicate rows considering the values of the second column of your dataset, type the code below:

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

Here, we have written Array(2) instead of Array(1). This Array(2) indicates the second column of our dataset. The second column of our dataset is the Department column.

• Save the code by pressing Ctrl + S.
• Press the F5 key to Run the code.
• Finally, you will see results like below.

Read More: Excel VBA: Remove Duplicates from an Array

2. VBA to Remove Duplicate Rows without Header in Excel

We can also use VBA to delete duplicate rows if our dataset doesn’t have any headers. We will use the same dataset here. But there will be no headers this time.

Follow the steps below to know more.

STEPS:

• In the first place, go to the Developer tab and select Visual Basic.
• The Visual Basic window will appear.

• Now, go to Insert and select Module.
• Then, write the VBA code in the Module window:

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

Here, the only difference with the previous codes is, that we have used Header:=xlNo instead of Header:=xlYes. Header:=xlNo indicates that there is no header in the selected dataset.

• Next, press Ctrl + S to save the code.
• After that, go to Macros. A Macro window will appear.

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

• Lastly, you will see results like below.

Read More: How to Remove Duplicate Rows Based on One Column Using Excel VBA

3. Apply VBA to Delete Similar Rows from an Excel Table

Sometimes, we have duplicate rows in a table and we need to delete them. We can also use VBA to solve this problem easily. To do so, we have converted our previous dataset into an Excel table. We will delete the duplicate rows from this table.

Let’s pay attention to the steps below.

STEPS:

• Firstly, select Visual Basic from the Developer The Visual Basic window will appear.

• Secondly, go to Insert and select Module.
• Type the 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 was Table1.

• Now, press Ctrl + S to save the VBA code.
• Then, select Macros from the Developer tab.

• After that, select the desired code from the Macro window and click Run.

• In the end, you will see results like below.

4. Delete Duplicate Rows from a Column in Excel with VBA

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

Let’s observe the steps below to learn this method.

STEPS:

• Go to the Developer and select Visual Basic at first.
• A Visual Basic window will appear.

• Now, go to Insert and select Module.
• After that, type the code in the Module window:

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

Here, we have selected column 2. That means it will search the duplicate values in the Department column. The variable x represents the range of the dataset and lstrw indicates the last row.

• To save the code, press Ctrl + S.
• Next, select Macros. It will open a window named Macro.

• After that, select the desired code from the Macro window and click Run.

• Lastly, you will see results like below.

5. VBA to Remove Identical Rows Keeping the Last Duplicate

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

Let’s follow the steps below to learn this technique.

STEPS:

• In the first place, select Visual Basic from the Developer tab.
• Instantly, the Visual Basic window will open.

• Next, go to Insert and select Module.
• After that, write the 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

Here, we have used If-Else inside a For loop. This code will take input from the user. We need to input the column where we want to look for duplicates. We cannot use this code for two columns at the same time.

• Press Ctrl + S to save the code.
• Now, select Macros from the Developer tab.

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

• After running the code, the Input window will occur.

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

• Finally, click OK to see results like below.

6. Delete Duplicate Rows Based on All Columns with Excel VBA

We can also delete duplicate rows based on all columns. That means values will have to be exactly the same in both rows. In this case, we will use the same kind of dataset here.

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.

Let’s observe the steps below.

STEPS:

• Firstly, go to the Developer tab and select Visual Basic.
• The Visual Basic window will appear.

• Now, go to Insert and select Module.
• Then, type the 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

Here, MyRng is a variable that denotes the range of our dataset. We have chosen all four columns in the RemoveDuplicates method.

• After that, press Ctrl + S to save the code.
• Again, go to the Macros from the Developer tab.

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

• Finally, you will see results like below.

Read More: Excel VBA: Remove Duplicates Comparing Multiple Columns

7. Apply VBA to Remove Identical Rows Based on Specific Columns

In this method, we will use VBA to delete duplicate rows based on specific columns. Again, we will use the previous dataset here.

Let’s follow the steps below.

STEPS:

• Firstly, go to the Developer tab and select Visual Basic. It will open the Visual Basic window.

• Now, go to Insert and select Module.
• Then, type the code in the Module window:

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

Here, to select multiple columns we have used Array(1, 2) instead of Array(1). Array(1, 2) selects columns 1 & 2 of our dataset.

• After that, press Ctrl + S to save the code.
• Then, select Macros from the Developer tab.

• Again, select the code and Run it from the Macro window.

• In the end, you will see results like below.

8. VBA to Remove Duplicate Rows in Excel after Sorting

In this last method, we will try to delete duplicate rows after sorting. So, we will use a dataset that contains the ID Number of the employees. But the numbers are not sorted. We will sort them and delete the duplicate rows.

Let’s pay attention to the steps below.

STEPS:

• Firstly, go to the Developer tab and select Visual Basic. The Visual Basic window will open.

• Secondly, go to Insert and select Module.
• Thirdly, type the 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

Here, we have used the Sort method to sort the dataset in ascending order. And also used the RemoveDuplicates method to delete duplicate rows.

• Now, press Ctrl + S to save the code.
• Then, select Macros from the Developer tab.

• After that, select the desired code and Run it from the Macro window.

• Finally, you will see results like below.

Download Practice Book

Download the practice book here.

Conclusion

We have demonstrated 8 easy and effective VBA codes to delete duplicate rows in Excel. I hope these methods will help you to solve your desired problems. Furthermore, you can also download the practice book to exercise more. Last of all, if you have any queries or suggestions, feel free to ask in the comment section.