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
Please how do I delete duplicates in columns "B:B" using this: oRange.RemoveDuplicates(Columns:=2)
ReplyDelete