Make Range
Advanced Filter cane be used for making the range with unique records. The following snippet should be useful for the same.
Sub Make_Unique_List_InPlace()
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
' ---------------------------------------------
Set oRange = Range("B:B")
oRange.AdvancedFilter(Action:=xlFilterInPlace, Unique:=True)
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")
Err.Clear()
Resume Next
End If
End Sub
oRange.AdvancedFilter filters or copies data from a list based on a criteria range. If the initial selection is a single cell, that cell's current region is used. The above code does not physically delete duplicate records from the sheet/range
Posts
