PPT Excel: How to remove duplicates in Excel using VBA

This post explains how to remove duplicates from a range of cells using excel VBA. Removing duplicates from a list, array, or range of cells is a basic operation in programming. You use if and for loop statements to remove duplicates.

Suppose you have received the sales table with product details and need to create a unique list of products. You have to remove duplicate products from the product field and paste it into a new column.

VBA Script

Sub uniqueItems()
'Declare Variables
Dim uniqueDataFlag As Boolean, uNum As Integer, dataRow As Integer, uniqRow As Integer

'First value will be unique so Place it to G2
Range("G2").Value = Range("A2").Value

uNum = 1
uniqueDataFlag = True

'you have to use for loop to check the values from A3 to A21
For dataRow = 3 To 21
    For uniqRow = 1 To uNum
        If Range("A" & dataRow).Value = Cells(uniqRow, 7).Value Then
            uniqueDataFlag = False
        End If
    Next uniqRow
    
    'if uniqueDataFlag is true, place cell value in column G and increase the count uNum = uNum + 1
    If uniqueDataFlag = True Then
        Cells(uNum + 1, 7).Value = Range("A" & dataRow).Value
        uNum = uNum + 1
    End If

'reset uniqueDataFlag to True
uniqueDataFlag = True

Next dataRow

End Sub

Copy and paste the above script into your VBA script module

Please follow the below steps to add a button to run your VBA Script

1. Add an auto shape and name it Remove Duplicates
2. Press the right button on the mouse to open the menu
3. Select Assign Macro… to popup Assign Macro dialog box
4. Choose the uniqueItems macro from the macro list
5. Press OK

Run Script

1. Type the text Product in cell G1
2. Press the Remove Duplicates button to execute the above VBA Script
3. You can see the unique list of products filled in column G

Conclusions

Multiple options are available in excel to remove the duplicates in excel. Remove the duplicates and list the unique items using VBA will help you to automate your process. Run the above script in your project, If you need to update a unique list of products whenever the data table is updated.