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

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.

Download Workbook


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

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 Microsoft Excel 365 version here, you can use any other versions 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 that 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

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


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 rows number of this dataset.

The 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 COUNTIF 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

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


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 rows 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

Read More: How to Delete Empty Rows at Bottom in Excel (7 Ways)


Similar Readings:


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 will be 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 function 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

Read More: Excel VBA Code to Delete Rows Based on Multiple Cell Value (3 Criteria)


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 that 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 Row Using VBA (14 Ways)


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


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

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo