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:

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

Test the code:

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

Sample File:

DeleteDups.zip 6.34KB 

Approved by mdmackillop


This entry has been viewed 591 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express