Excel Forum

Delete Duplicate Values from a Column in Excel

This is a very simple macro that will delete duplicate values from a column in Excel.  This is a no-frills, straightforward and simple, remove-duplicates macro.

You use it by first selecting a cell within the column that contains the duplicates that you want to remove.  Then, just run the macro and it will do the rest.  This assumes that your data starts in row 1, but it should not hurt if it starts in a higher row.  Also, this assumes that you are checking for and removing duplicates from the entire column; so, if you have data below the duplicates that you do not want to be included in this calculation, make sure to move it before running the macro.

Be very careful to select a cell in the correct column before running this macro.  If you accidentally put it into the wrong column and remove data, that cannot be undone.


Where to install the macro: Module


Delete Duplicate Values from a Column in Excel

Select All
Sub Delete_Duplicates_Column()
Dim selected_column As Long
Dim first_row As Long
Dim last_row As Long

Application.ScreenUpdating = False

selected_column = Selection.Column

first_row = 1
last_row = Cells(Rows.Count, selected_column).End(xlUp).Row

For i = last_row To first_row Step -1

    If Application.WorksheetFunction.CountIf(Range(Cells(first_row, selected_column), Cells(i, selected_column)), Cells(i, selected_column).Value) > 1 Then
        Cells(i, selected_column).EntireRow.Delete
    End If

Next i

Application.ScreenUpdating = True

End Sub