Remove Duplicates

Remove Duplicates

[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]
Range.RemoveDuplicates ‘Method removes duplicate values from a range of values.

Syntax for Range.RemoveDuplicates

[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]
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.

Remove Duplicate Rows

Following is a macro to remove duplicates from within a range.

[sourcecode language=”vb” light=”true” wraplines=”false” gutter=”false”]
Sub RemoveDuplicatesInRange()

Dim oWS As Worksheet ‘ Worksheet Object
Dim oRange As Range ‘ Range Object –
‘Content Represents the List of Items that need to be made unique

On Error GoTo Disp_Error
oWS = ActiveSheet
oRange = oWS.UsedRange

If Not oRange Is Nothing Then oRange = Nothing
If Not oWS Is Nothing Then oWS = Nothing
Disp_Error: ‘ Error Handling
If Err <> 0 Then
MsgBox(Err.Number & " – " & Err.Description, vbExclamation, "FEI")
Resume Next
End If
End Sub