VBA Express : Excel - Delete Duplicates In A Column

Excel

Delete Duplicates In A Column

Ease of Use

Easy

Version tested with

2002

Submitted by:

Jacob Hilderbrand

Description:

Delete all duplicates in a Column.

Discussion:

You have many rows of data, but you only want unique data. This macro will delete all the duplicate rows. It checks Column A for duplicate entries and deletes all but one. You can change the column by changing the ranges in the code.

Code:

instructions for use

			Option Explicit 
 
Sub DeleteDups() 
     
    Dim x               As Long 
    Dim LastRow         As Long 
     
    LastRow = Range("A65536").End(xlUp).Row 
    For x = LastRow To 1 Step -1 
        If Application.WorksheetFunction.CountIf(Range("A1:A" & x), Range("A" & x).Text) > 1 Then 
            Range("A" & x).EntireRow.Delete 
        End If 
    Next x 
     
End Sub

How to use:

Open Excel.
Alt + F11 to open the Visual Basic Editor (VBE).
Insert-Module.
Paste the code.
Close the VBE (Alt + Q or the X in the top-right corner).

Test the code:

In Column A add any data.
Tools-Macro-Macros
Select DeleteDups and press Run.

Sample File:

DeleteDups.zip 6.34KB

Approved by mdmackillop

This entry has been viewed 624 times.