Subscribe for Weekly Tutorials
BONUS: subscribe now to download our Top Tutorials Ebook!
Delete Duplicate Rows
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
Subscribe for Weekly Tutorials
BONUS: subscribe now to download our Top Tutorials Ebook!
Tutorial: How to delete duplicate values from a data set in all versions of Excel. This includes E...
Macro: This macro will allow you to specify certain criteria and then to delete rows based up...
Tutorial: Quickly find all rows in Excel that contain a certain value and then delete those rows. ...
Macro: This macro will delete hidden rows from every worksheet in an entire workbook. However...
Macro: This is a macro which will delete blank rows in excel. This version will delete an entire ...
Macro: This macro will delete only completely blank rows in an excel spreadsheet. It allows you t...
How to Install the Macro
- Select and copy the text from within the grey box above.
- Open the Microsoft Excel file in which you would like the Macro to function.
- Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.
Or For other ways to get there, Click Here.
- 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.
- If the Macro goes in a Module, Click Here, otherwise continue to Step 8.
- If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.
- If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 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.
- You are now ready to run the macro.
