Duplicate value removal across sheets and rows and columns IN A FILE

[jiijjuu]

Since I could find much of a straight answer in my googling, I am turning in here for an expert help.


I have an excel file with almost 15 sheets in it, and each sheet has more than 17 columns filled with different number combinations which goes down in various row length. I am in need of help to retain only the values in cells which are unique. I need all repetitions to be deleted across columns, rows and sheets. So in effect a number like 509 should be there only one time across the whole file


Can you pls help me with your expertise.
Thanks in advance.

[mana]

Option Explicit

Sub test()
    Dim dic1 As Object
    Dim dic2 As Object
    Dim ws As Worksheet
    Dim c As Range
    Dim s, it
    
    Set dic1 = CreateObject("scripting.dictionary")
    Set dic2 = CreateObject("scripting.dictionary")
    
    For Each ws In Worksheets
        For Each c In ws.Cells(1).CurrentRegion
            s = c.Value
            If dic1.exists(s) Then
                c.ClearContents
                Set dic2(s) = dic1(s)
            Else
                Set dic1(s) = c
            End If
        Next
    Next
    
    For Each it In dic2.items
        it.ClearContents
    Next
    
End Sub

[1819]

Cross posted here:

http://www.excelguru.ca/forums/showthread.php?6820-Duplicate-value-removal-across-sheets-and-rows-and-columns&p=27902#post27902
http://www.mrexcel.com/forum/excel-q...umns-file.html


http://www.excelforum.com/showthread...=1#post4497560