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
Posts
Hi..
ReplyDeleteI need Remove the Duplicate rows from asheet using VBA code.
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
ReplyDeleteVery nice information about removing duplicate records by using Excel VBA.
ReplyDeleteThanks 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.
Anyone know how to do this w/o hardcoding the Column parameter?
ReplyDelete'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
Hi! that's nice, but for people who want to do it without VBA, I have to ways:
ReplyDeletehttp://runakay.blogspot.com/2011/02/feature-on-excel-20072010-to-remove.html
It is wrong. Unless you have an old excel version, use Data - Remove Duplicates from the menu.
DeletePlease how do I delete duplicates in columns "B:B" using this: oRange.RemoveDuplicates(Columns:=2)
ReplyDeleteCan the method return a range object?
ReplyDeleteThanks 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.
ReplyDeleteHi, How does one remove duplicate entries based on the following:
ReplyDeleteIf each row has matching 2 out of 4 (this can be up to 10 columns) columns, then remove duplicate row entry.
This is INCORRECT. Columns is REQUIRED
ReplyDeletehttps://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-removeduplicates-method-excel