Sharing some useful tips, solutions and notes for Geeks.

Friday, June 21, 2013

Remove duplicate values case sensitive using macro


Excel have default duplicate removing function inbuilt. But the major draw back is that if the column have following values

Column A

it will return only Blue. Since there is no checking of case sensitive values, it will be lost.
So for those who needed to remove duplicate values with case sensitive form can use below macro code.

Sub test()
    Dim a, e, x
    With Range("a1", Range("a" & Rows.Count).End(xlUp))
        a = .Value
        With CreateObject("Scripting.Dictionary")
            For Each e In a
                .Item(e) = Empty
            x = .keys
        End With
        .Resize(UBound(x) + 1).Value = Application.Transpose(x)
    End With
End Sub

Hope this will help you..

Dont leave me alone..comments please.. :)

No comments: