New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Remove / Delete Duplicate Values from a Column in Excel

Free Excel Macros

Remove / Delete Duplicate Values from a Column in Excel

Postby Don » Tue Jan 31, 2012 10:27 pm

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


Code: 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
Don
Moderator
 
Posts: 733
Joined: Dec 4, 2011
Reputation: 2
Excel Version: 2010

Return to Excel Macros

Who is online

Users browsing this forum: No registered users and 36 guests