How to Delete Duplicate Rows by VBA in Excel

Written by stephanie ellen
  • Share
  • Tweet
  • Share
  • Email

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:


  1. 1

    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."

  2. 2

    Open the VBE by pressing "Alt" and "F11" together on the keyboard. This opens the VBE window.

  3. 3

    Click on "Open" then click on "Module." A blank window will open in the VBE.

  4. 4

    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)

    End Sub

  5. 5

    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.

Don't Miss

  • All types
  • Articles
  • Slideshows
  • Videos
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the site, you consent to the use of cookies. For more information, please see our Cookie policy.