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

Get FREE Advanced Excel Exercises with Solutions!

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.

Delete Identical Rows with Header in Excel with VBA


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.

Delete Identical Rows with Header in Excel with VBA

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.

Delete Identical Rows with Header in Excel with VBA

  • 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

Delete Identical Rows with Header in Excel with VBA

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.

Delete Identical Rows with Header in Excel with VBA

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

Delete Identical Rows with Header in Excel with VBA

  • After that, you will see results like below.

Delete Identical Rows with Header in Excel with VBA

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

Delete Identical Rows with Header in Excel with VBA

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.

Delete Identical Rows with Header in Excel with VBA

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.

VBA to Remove Duplicate Rows without Header in Excel

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.

VBA to Remove Duplicate Rows without Header in Excel

  • 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

VBA to Remove Duplicate Rows without Header in Excel

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.

VBA to Remove Duplicate Rows without Header in Excel

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

VBA to Remove Duplicate Rows without Header in Excel

  • Lastly, you will see results like below.

VBA to Remove Duplicate Rows without Header in Excel

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.

Apply VBA to Delete Similar Rows from an Excel Table

Let’s pay attention to the steps below.

STEPS:

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

Apply VBA to Delete Similar Rows from an Excel Table

  • 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

Apply VBA to Delete Similar Rows from an Excel Table

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.

Apply VBA to Delete Similar Rows from an Excel Table

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

Apply VBA to Delete Similar Rows from an Excel Table

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

Apply VBA to Delete Similar Rows from an Excel Table


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.

Delete Duplicate Rows from a Column in Excel with VBA

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.

Delete Duplicate Rows from a Column in Excel with VBA

  • 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

Delete Duplicate Rows from a Column in Excel with VBA

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.

Delete Duplicate Rows from a Column in Excel with VBA

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

Delete Duplicate Rows from a Column in Excel with VBA

  • Lastly, you will see results like below.

Delete Duplicate Rows from a Column in Excel with VBA


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.

VBA to Remove Identical Rows in Excel Keeping the Last Duplicate

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.

VBA to Remove Identical Rows in Excel Keeping the Last Duplicate

  • 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

VBA to Remove Identical Rows in Excel Keeping the Last Duplicate

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.

VBA to Remove Identical Rows in Excel Keeping the Last Duplicate

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

VBA to Remove Identical Rows in Excel Keeping the Last Duplicate

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

VBA to Remove Identical Rows in Excel Keeping the Last Duplicate

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

VBA to Remove Identical Rows in Excel Keeping the Last Duplicate

  • Finally, click OK to see results like below.

VBA to Remove Identical Rows in Excel Keeping the Last Duplicate


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.

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.

Let’s observe the steps below.

STEPS:

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

Delete Duplicate Rows Based on All Columns with Excel VBA

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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mursalin Ibne Salehin
Mursalin Ibne Salehin

Mursalin Ibne Salehin holds a BSc in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. Over the past 2 years, he has actively contributed to the ExcelDemy project, where he authored over 150 articles. He has also led a team with content development works. Currently, he is working as a Reviewer in the ExcelDemy Project. He likes using and learning about Microsoft Office, especially Excel. He is interested in data analysis with Excel, machine learning,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo