Microsoft Visual Basic for Applications (VBA) is a user-friendly programming language that can customise Microsoft Office products like Microsoft Excel. Instead of poring through an Excel spreadsheet to identify duplicate rows, an Excel macro can identify any duplicate rows. An Excel macro is a short piece of VBA code that you can run directly from the Visual Basic Editor.
- Skill level:
Other People Are Reading
Select the columns of data that you want to compare for duplicate rows. For example, if you want to check column A for duplicate rows, click on "A."
Open the VBE by pressing "Alt" and "F11" together on the keyboard. This opens the VBE window.
Click on "Open" then click on "Module." A blank window will open in the VBE.
Cut and paste the following code into the blank window from Step 3:
Public Sub DeleteDuplicateRows() ' DeleteDuplicateRows
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
Application.StatusBar = False Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic MsgBox "Duplicate Rows Deleted: " & CStr(N)
Press "F5" to run the procedure. The macro will run and delete any duplicate rows.
Tips and warnings
- This macro will delete all duplicate rows in the same column, except for the lowest number record. Save a copy of your data before running the macro in case you don't get the results you were expecting.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for