Excel

Delete Duplicated Entries In Cells

Ease of Use

Easy

Version tested with

2000, 2003 

Submitted by:

johnske

Description:

Any duplicated entries are deleted and a message box tells you how many were deleted. 

Discussion:

You may have a list of things, but you want to ensure each entry is unique. By default, this searches from left to right and top to bottom (as in normal reading order) and deletes any duplicated entries, leaving only the first such entry found. 

Code:

instructions for use 

			
Option Explicit 
 
Sub DeleteDuplicateEntries() 
    Dim Cell As Range, Cel As Range, N& 
    Application.ScreenUpdating = False 
    N = 0 
    For Each Cell In Selection 
         '1st loop - (to speed things up ignore any empty cells)
        If Cell <> Empty Then 
            For Each Cel In Selection 
                 '2nd loop - compare non-empty cel values
                 'and clear contents if it's a duplicated value
                If Cel <> Empty And _ 
                Cel.Value = Cell.Value And _ 
                Cel.Address <> Cell.Address Then 
                    Cel.ClearContents 
                    N = N + 1 
                End If 
            Next Cel 
        End If 
    Next 
    Application.ScreenUpdating = True 
    MsgBox "There were " & N & " duplicated entries deleted" 
End Sub

How to use:

  1. Open an Excel workbook
  2. Select Tools/Macro/Visual Basic Editor
  3. In the VBE window, select Insert/Module
  4. Copy and paste the code above into the Module
  5. Now select File/Close and Return To Microsoft Excel
  6. Dont forget to save your changes...
 

Test the code:

  1. Select the area where there may be duplicates you want deleted
  2. Go to Tools/Macro/Macros.../DeleteDuplicateEntries/Run
  3. (Or download the attachment to test this out)
 

Sample File:

DeleteDuplicates.zip 6.71KB 

Approved by mdmackillop


This entry has been viewed 227 times.

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