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.

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.

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()

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.

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

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.

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.

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

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.

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.

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

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

Similar Readings:

• How to Delete Hidden Rows in Excel (3 Methods)
• Delete Alternate Rows in Excel (5 Quick Ways)
• How to Delete Unused Rows in Excel (8 Easy Ways)
• Delete Rows Based on Another List in Excel (5 Methods)
• Excel VBA: Delete Row If Cell Is Blank (A Complete Guide)

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

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

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.

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.

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

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.

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

• How to Filter and Delete Rows with VBA in Excel (2 Methods)
• Delete Selected Rows with Excel VBA (A Step-by-Step Guideline)
• How to Delete Unused Rows in Excel (8 Easy Ways)
• Delete Alternate Rows in Excel (5 Quick Ways)
• How to Delete Filtered Rows in Excel (5 Methods)