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 www.vbadud.blogspot.com

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

oWS = ActiveSheet

oRange = oWS.UsedRange

oRange.RemoveDuplicates(Columns:=2)

If Not oRange Is Nothing Then oRange = Nothing

If Not oWS Is Nothing Then oWS = Nothing

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

' Error Handling

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

Disp_Error:

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.

Syntax

expression.RemoveDuplicates(Columns, Header)

expression A variable that represents a Range object.

Parameters

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 http://vbadud.blogspot.com/2007/08/detecting-duplicate-values-excel-vba.html)




Excel Range before Removing Duplicates

Excel Range after running the Macro

10 comments:

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

    ReplyDelete
  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

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

    ReplyDelete
  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

    ReplyDelete
  5. Anonymous10:47 AM

    Hi! that's nice, but for people who want to do it without VBA, I have to ways:
    http://runakay.blogspot.com/2011/02/feature-on-excel-20072010-to-remove.html

    ReplyDelete
    Replies
    1. Anonymous11:25 AM

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

      Delete
  6. Anonymous7:09 AM

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

    ReplyDelete
  7. Anonymous8:38 AM

    Can the method return a range object?

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

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

    ReplyDelete

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...

Visual Basic for Applications (VBA) Forum (recent threads)

CodeKeep VBA Feed

Visual Studio Tools for Office Forum (recent threads)

Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.

Office Business Applications (OBA) Team Blog

MSDN Code Gallery Published Resources For Tag VSTO

microsoft.public.vsnet.vstools.office Google Group