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.

Excel VBA remove duplicate rows based on one column

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.

Excel VBA remove duplicate rows based on one column

Step-01:
➤ Go to Developer Tab >> Visual Basic Option.

using RemoveDuplicates

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

Excel VBA remove duplicate rows based on one column

After that, a Module will be created.

using RemoveDuplicates

Step-02:
➤ Write the following code

Sub RemoveDuplicaterow1()

Range("B3:D11").CurrentRegion.RemoveDuplicates Columns:=Array(1), Header:=xlYes

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.

using RemoveDuplicates

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

Excel VBA remove duplicate rows based on one column


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.

Excel VBA remove duplicate rows based on one column

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.

bottom to top

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

bottom to top


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.

Excel VBA remove duplicate rows based on one column

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.

Top to Bottom

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

Top to Bottom


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.

Excel VBA remove duplicate rows based on one column

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.

removing duplicate rows including the first value

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

removing duplicate rows including the first value


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.

Excel VBA remove duplicate rows based on one column

Steps:
➤ Follow Step-01 of Method-1.
➤ Write the following code

Sub RemoveDuplicaterow5()

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

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.

Table

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

Table

Read More: How to Delete Duplicate Rows in Excel with VBA


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.

Practice


Download Workbook


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.


Related Articles

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

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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo