Sunday, July 20, 2008

Remove Duplicate Rows from a Sheet using Excel VBA

Remove Duplicate Records in Excel Table using Excel VBA

RemoveDuplicates is one of the most sought functions; thanks to Microsoft for implementing it in Excel 2007. Here we go

Sub Remove_Duplicates_In_A_Range()

Dim oWS As Worksheet ' Worksheet Object

Dim oRange As Range ' Range Object - Contains Represents the List of Items that need to be made unique

On Error GoTo Disp_Error

' ---------------------------------------------

' Coded by Shasur for

' ---------------------------------------------

oWS = ActiveSheet

oRange = oWS.UsedRange


If Not oRange Is Nothing Then oRange = Nothing

If Not oWS Is Nothing Then oWS = Nothing

' --------------------

' Error Handling

' --------------------


If Err <> 0 Then

MsgBox(Err.Number & " - " & Err.Description, vbExclamation, "VBA Tips & Tricks Examples")

Resume Next

End If

End Sub

Range.RemoveDuplicates Method removes duplicate values from a range of values.


expression.RemoveDuplicates(Columns, Header)

expression A variable that represents a Range object.


Columns Optional Variant Array of indexes of the columns that contain the duplicate information. If nothing is passed then it assumes all columns contain duplicate information.

Header Optional XlYesNoGuess Specifies whether the first row contains header information. xlNo is the default value; specify xlGuess if you want Excel to attempt to determine the header.

This method is available from Version 2007 only (users of older version need to put-up with some custom coding like

Excel Range before Removing Duplicates

Excel Range after running the Macro


  1. Hi..
    I need Remove the Duplicate rows from asheet using VBA code.

  2. If you are using Excel 2007 then there is a remove duplicates option. If it is older version you need to write a small Subroutine

  3. Very nice information about removing duplicate records by using Excel VBA.
    Thanks for sharing, really like your view.
    I can see that you are putting a lot of time and effort into your blog.

    Keep posting the good work.

  4. Anonymous4:26 PM

    Anyone know how to do this w/o hardcoding the Column parameter?

    'what do I set dupColumns to that actually works - please try it out in VBA as I've never gotten a responsd that works! Thanks!

    Range("A10:C15").RemoveDuplicates Columns:=dupColumns, Header:=xlYes

  5. Anonymous10:47 AM

    Hi! that's nice, but for people who want to do it without VBA, I have to ways:

    1. Anonymous11:25 AM

      It is wrong. Unless you have an old excel version, use Data - Remove Duplicates from the menu.

  6. Anonymous7:09 AM

    Please how do I delete duplicates in columns "B:B" using this: oRange.RemoveDuplicates(Columns:=2)

  7. Anonymous8:38 AM

    Can the method return a range object?

  8. Anonymous3:48 PM

    Thanks for this. I was trying to confirm exactly what is removed. 'Entire rows' is the answer I was looking for, and your illustration shows this, where microsoft documentation did not specify.

  9. Hi, How does one remove duplicate entries based on the following:

    If each row has matching 2 out of 4 (this can be up to 10 columns) columns, then remove duplicate row entry.

  10. This is INCORRECT. Columns is REQUIRED


Share on Facebook
Related Posts Plugin for WordPress, Blogger...
Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.