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.


Download Practice Book

Download the practice book here.


8 Ways to Delete Duplicate Rows in Excel with VBA

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: How to Delete Rows in Excel: 7 Methods


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, 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 Delete Specific Rows in Excel (8 Quick Ways)


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

Read More: VBA to Delete Entire Row based on Cell Value in Excel (3 Methods)


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

Read More: Excel Shortcut to Delete Rows (With Bonus Techniques)


Similar Readings:


5. VBA to Remove Identical Rows Keeping the Last Duplicate

In the previous methods, we have 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 know 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

Related Content: How to Filter and Delete Rows with VBA in Excel (2 Methods)


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: Formula to Remove Blank Rows in Excel (5 Examples)


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

Read More: Macro to Delete Row in Excel If Cell is Blank


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.

Read More: How to Delete Filtered Rows in Excel (5 Methods)


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.


Related Articles

Mursalin

Mursalin

Hi there! This is Mursalin. I am an Excel and VBA content developer at ExcelDemy. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I am currently working and doing research on Microsoft Excel. Here I will be posting articles related to Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo