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
