Delete Duplicate Rows

Add to Favorites
This macro will delete rows that appear twice in a list or worksheet. If two cells are identical, this macro will delete every row with an identical cell which is underneath the first occurrence. The first instance will still remain, but all identical cells in the rows underneath the original cell will be deleted. In order to run the macro, you need to select the entire column where you want to search for duplicates and then run the macro.

Where to install the macro:  Module

Delete Duplicate Rows in a Column

Public Sub DeleteDuplicateRows()

' This macro will delete all duplicate rows which reside under
‘the first occurrence of the row.

‘Use the macro by selecting a column to check for duplicates
‘and then run the macro and all duplicates will be deleted, leaving
‘the first occurrence only.

Dim R As Long
Dim N As Long
Dim V As Variant
Dim Rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set Rng = Application.Intersect(ActiveSheet.UsedRange, _
                    ActiveSheet.Columns(ActiveCell.Column))
Application.StatusBar = "Processing Row: " & Format(Rng.Row, "#,##0")
N = 0
For R = Rng.Rows.Count To 2 Step -1
If R Mod 500 = 0 Then
    Application.StatusBar = "Processing Row: " & Format(R, "#,##0")
End If

V = Rng.Cells(R, 1).Value

If V = vbNullString Then
    If Application.WorksheetFunction.CountIf(Rng.Columns(1), vbNullString) > 1 Then
        Rng.Rows(R).EntireRow.Delete
        N = N + 1
    End If
Else
    If Application.WorksheetFunction.CountIf(Rng.Columns(1), V) > 1 Then
        Rng.Rows(R).EntireRow.Delete
        N = N + 1
    End If
End If
Next R

EndMacro:

Application.StatusBar = False
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox "Duplicate Rows Deleted: " & CStr(N)

End Sub






Similar Content on TeachExcel
Delete Duplicate Values in All Versions of Excel
Tutorial: How to delete duplicate values from a data set in all versions of Excel.  This includes Ex...
Delete Entire Rows Based on Predefined Criteria (Text)
Macro: This macro will allow you to specify certain criteria and then to delete rows based up...
Delete All Rows that Contain a Specific Value in Excel
Tutorial: Quickly find all rows in Excel that contain a certain value and then delete those rows. ...
Delete Hidden Rows in a Workbook
Macro: This macro will delete hidden rows from every worksheet in an entire workbook. However...
Delete Blank Rows in Excel
Macro: This is a macro which will delete blank rows in excel. This version will delete an entire ...
Delete Rows in Excel if Completely Empty
Macro: This macro will delete only completely blank rows in an excel spreadsheet. It allows you t...



How to Install the Macro
  1. Select and copy the text from within the grey box above.

  2. Open the Microsoft Excel file in which you would like the Macro to function.

  3. Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.  Or For other ways to get there, Click Here.

  4. On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject (YOUR FILE'S NAME HERE) and click this.

  5. If the Macro goes in a Module, Click Here, otherwise continue to Step 8.

  6. If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.

  7. If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.

  8. Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.

  9. You are now ready to run the macro.


Similar Content
Delete Duplicate Values in All Versions of Excel
Tutorial: How to delete duplicate values from a data set in all versions of Excel.  This includes Ex...
Delete Entire Rows Based on Predefined Criteria (Text)
Macro: This macro will allow you to specify certain criteria and then to delete rows based up...
Delete All Rows that Contain a Specific Value in Excel
Tutorial: Quickly find all rows in Excel that contain a certain value and then delete those rows. ...
Excel Forum