How to Delete Duplicate Rows in Excel Using VBA

Written by grahame turner
  • Share
  • Tweet
  • Share
  • Email

Certain spreadsheets are used and updated a lot. These often are filled with data that is repeated multiple times. There are ways to find and delete any rows that contain that duplicate data, but one of the most simple and automatic ways to accomplish this is with a Visual Basic Macro. Excel contains an application for programming called Excel VBA, which can be used to make any number of Macros---pieces of code designed to do a custom job whenever summoned.

Skill level:

Things you need

  • Microsoft Excel

Show MoreHide


  1. 1

    Open your spreadsheet in Excel and open your VBA editor by pressing Alt and the F11 key on your computer.

  2. 2

    Start a new project by right-clicking your workbook on the right-hand menu and choosing the "New" option.

  3. 3

    Start the new function with "Sub DeleteDuplicateRows()." This informs the VBA compiler that there is a new subroutine that it should be aware of.

  4. 4

    Create the variables with the following code:

    Dim Row As Long Dim Count As Long Dim Compare As Variant Dim Range As Range

    This tells the system to set aside the four variables (Row, Count, Comparison and Range) to use in the code. You can use whatever variable names make more sense to you, but remember to change them in the later code.

  5. 5

    Set up the macro with some more basic code:

    On Error GoTo EndMacro Application.ScreenUpdating = False Application.Calculation = xlCalculationManual

    Set Range = Application.Intersect(ActiveSheet.UsedRange, _ ActiveSheet.Columns(ActiveCell.Column)) Application.StatusBar = "Processing Row: " & Format(Rng.Row, "#,##0")

    The first group of code tells the system to run the macro if it runs into any problems. The second group sets the range variable according to whatever the user has selected.

  6. 6

    Type in the following code:

    Count = 0 For Row = Range.Rows.Count To 2 Step -1 If Row Mod 500 = 0 Then Application.StatusBar = "Processing Row: " & Format(R, "#,##0") End If

    Compare = Range.Cells(Row, 1).Value

    This tells the system to step through each of the rows selected by the user, and change the Compare variable to that row each time it moves on.

  7. 7

    Input the following code next:

    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

    These statements are the ones that actually tell the system to compare the Compare variable to the next cell and delete the row if the two are the same.

  8. 8

    Press enter to insert a couple of new lines, and then type in "Next Row". This tells the system to stop looking at the first row, and move onto the next.

  9. 9

    Close out the function with:


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

    End Sub

    These final lines tell the system how to close the subroutine out, and also to display a helpful message box informing the user that the Macro actually did what it was supposed to do.

  1. 1

    Save the spreadsheet. Save a second, backup copy before using the Macro. Either copy and paste the file into a backup folder, or us the "Save as" file to save the second copy---you will need to close Excel and reopen it.

  2. 2

    Click and drag your cursor around the most important column of data. This is the one for which all entries should differ---as in, if everything had been entered correctly, there wouldn't be any duplicate entries.

  3. 3

    Open the "Tools" menu and select "Macros." This opens the macros dialogue, allowing you to choose and run your macro.

  4. 4

    Select your macro; if you used the example above, it will be called "DelDuplicateR." Then press the "Run" button. The Macro will run automatically, and pop up a message indicating that the duplicates have been deleted.

Tips and warnings

  • This only works if the content of the two cells are exactly the same. It will not work if they differ by even a small amount.
  • Macros cannot be undone using the "Undo" command under the "Edit" menu or Ctrl and Z. Before using any macros, you should back up your data to prevent losing it entirely.

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.