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

Get FREE Advanced Excel Exercises with Solutions!

If you are trying to remove duplicate rows based on one column with the help of VBA Excel for your dataset, then you will find this article helpful. So, letâ€™s start our main article.

Remove Duplicate Rows Based on One Column in Excel Using VBA: 5 Ways

Here, we have multiple rows for a product Apple of the Product column, and based on this column we will show the ways to remove the duplicate rows for the product Apple using VBA codes.

We have used the Microsoft Excel 365 version here, you can use any other version according to your convenience.

Method-1: Using VBA Remove Duplicates to Remove Duplicate Rows Based on One Column

Here, we will remove the rows containing Apple for the second and third times which means Row 6 and Row 10 by using the Remove Duplicates method.

Step-01:
âž¤ Go to Developer Tab >> Visual Basic Option.

Then, the Visual Basic Editor will open up.
âž¤ Go to Insert Tab >> Module Option.

After that, a Module will be created.

Step-02:
âž¤ Write the following code

Sub RemoveDuplicaterow1()

End Sub

Here, the range of our dataset is B3:D11, Columns:=Array(1) indicates the first column of this range which means Column B on the basis of which we will remove duplicates, Header:=xlYes is for the header of this dataset.

âž¤ Press F5.
Then, you will be able to remove the duplicate rows of the Product Apple except for the first row of this product.

Method-2: Remove Duplicate Rows Based on One Column from Bottom to Top

Like the previous method, here we will also remove the duplicate rows for the Product Apple from the bottom rows to the top rows except for the initial row containing Apple, but here we will not use the RemoveDuplicates method rather than use a different code for this purpose.

Steps:
âž¤ Follow Step-01 of Method-1.
âž¤ Write the following code

Sub RemoveDuplicaterow2()

Dim Rng As Range
Dim x As Long
Set Rng = Range("B4", Range("B" & Rows.Count).End(xlUp))
x = Rng.Rows.Count

For x = x To 1 Step -1
Â  Â  With Rng.Cells(x, 1)
Â  Â  Â  Â If WorksheetFunction.CountIf(Rng, .Value) > 1 Then
Â  Â  Â  Â  Â  Â  Â  Â  Â  .EntireRow.Delete
Â  Â  Â  Â End If
Â  Â  Â End With
Next x

End Sub

Here, we have declared Rng as Range, x as Long, and declared the range from cell B4 to the last used row of this column and after that, we have assigned x to the total row number of this dataset.

The VBA FOR loop will execute the removal of the duplicate rows from the last row to the first row of this range with a reverse order Step -1 and the COUNTIF function will determine if the same value is repeated or not and then the rows with duplicate values will be deleted.

âž¤ Press F5.
Afterward, you will be able to remove the duplicate rows of the Product Apple except for the first row of this product.

Method-3: Excel VBA to Remove Duplicate Rows Based on One Column from Top to Bottom

Here, we will remove the rows with duplicate Product Apple from the top row to the bottom row, Row 4 and Row 6, except for the very last row with Apple.

Steps:
âž¤ Follow Step-01 of Method-1.
âž¤ Write the following code

Sub RemoveDuplicaterow3()

Dim Rng As Range
Dim x As Long
Set Rng = Range("B4", Range("B" & Rows.Count).End(xlUp))
x = Rng.Rows.Count

For x = 1 To x Step 1
Â  Â With Rng.Cells(x, 1)
Â  Â  Â  Â  If WorksheetFunction.CountIf(Rng, .Value) > 1 Then
Â  Â  Â  Â  Â  Â  Â  Â  .EntireRow.Delete
Â  Â  Â  Â  End If
Â  Â  End With
Next x

End Sub

Here, we have declared Rng as Range, x as Long, and declared the range from cell B4 to the last used row of this column and after that, we have assigned x to the total row number of this dataset.

The FOR loop will execute the removal of the duplicate rows from the first row to the last row of this range with Step 1 and COUNTIF will determine if the same value is repeated or not and then the rows with duplicate values will be deleted.

âž¤ Press F5.
Finally, you will be able to remove the duplicate rows of the Product Apple except for the last row of this product.

Method-4: Remove All the Duplicate Rows Including the First Value

In this section, we will try to remove all of the duplicate rows of the Product Apple including the initial row of this product.

Steps:
âž¤ Follow Step-01 of Method-1.
âž¤ Write the following code

Sub RemoveDuplicaterow4()

Dim ws As Worksheet
Dim X As Range
Dim Y As Range
Set ws = Worksheets("Removeall")

For Each X In ws.Range("B3:B11").Cells
If WorksheetFunction.CountIf(ws.Columns("B"), X.Value) > 1 Then
Â  Â  If Not Y Is Nothing Then
Â  Â  Â  Â Set Y = Union(Y, X)
Â  Â  Else
Â  Â  Â  Â Set Y = X
Â  Â  End If
End If
Next X

If Not Y Is Nothing Then Y.EntireRow.Delete

End Sub

Here, we have declared ws as Worksheet, X, Y as Range, and assigned the Removeall worksheet to ws. The FOR loop will work for each cell of the range B3:B11, and X is assigned to cells of this range, when the value of the cells is repeated we will get the number of this value greater than 1, and then we will first assign Y to X, when Y will not be empty then we will join the X and Y ranges together by UNION commandÂ and then we will delete the rows.

âž¤ Press F5.
Then, we will get the following dataset which has no rows for the Product Apple.

Method-5: Using Excel VBA to Remove Duplicate Rows Based on One Column for a Table

Here, we have a Table whose name is Table1,Â and using a VBA code we will remove the rows containing Apple for the second and third times which means Row 6 and Row 10 except for the first row of this product.

Steps:
âž¤ Follow Step-01 of Method-1.
âž¤ Write the following code

Sub RemoveDuplicaterow5()

ActiveSheet.ListObjects("Table1").DataBodyRange.RemoveDuplicates _

End Sub

Here, Table1 is the table name, Columns:=Array(1) indicates the first column of this table which means Column B on the basis of which we will remove duplicates, Header:=xlYes is for the header of this dataset.

âž¤ Press F5.
Then, you will be able to remove the duplicate rows of the Product Apple except for the first row of this product.

Practice Section

For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

Conclusion

In this article, we tried to cover the ways to remove duplicate rows based on one column using VBA easily. Hope you will find it useful. If you have any suggestions or questions, feel free to share them 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.
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF