VBA Express : Excel - Remove Duplicates

Excel

Remove Duplicates

Ease of Use

Easy

Version tested with

2003

Submitted by:

rafaelsg

Description:

Remove the entire row based on values of the current column

Discussion:

Considering a Full extraction where you want to filter only the unique values according to an specific column.

Code:

instructions for use

			Sub DeleteDupsCurCol() 
     'Remove entire row in case a value on the CURRENT column already exists.
     'code based on DRJ user code, on this site
     
    Dim x               As Long 
    Dim LastRow         As Long 
    col = ActiveCell.Column 
    LastRow = Cells(65536, col).End(xlUp).Row 
     'LastRow = Range("A65536").End(xlUp).Row
    For x = LastRow To 1 Step -1 
        If Application.WorksheetFunction.CountIf(Range(Cells(1, col), Cells(x, col)), Cells(x, col).Text) > 1 Then 
            Cells(x, col).EntireRow.Delete 
        End If 
    Next x 
     
End Sub

How to use:

copy the code
paste into a existing or new module
click on a cell of the column you want remove duplicated values
Go tools/Macros/Macros...
Select DeleteDupsCurCol and Run.

Test the code:

copy the code
paste into a existing or new module
click on a cell of the column you want remove duplicated values
Go tools/Macros/Macros...
Select DeleteDupsCurCol and Run.

Sample File:

No Attachment

Approved by Jacob Hilderbrand

This entry has been viewed 386 times.