Unique List From All worksheet.

Get Unique Element from all worksheet at new sheet

Sub GetUnique()
    Dim wksSheet As Worksheet
    Dim VarArr
    Dim lngCOunt As Long
    With CreateObject(“Scripting.Dictionary”)
    For Each wksSheet In ThisWorkbook.Worksheets
        VarArr = wksSheet.Range(“A1”).CurrentRegion
        For Each kk In VarArr
            .Item(kk) = 0
        Next kk
    Next wksSheet
    VarArr = Application.Transpose(.keys)
    End With
    Set wksSheet = ThisWorkbook.Worksheets.Add
    wksSheet.Name = “Unique”
    wksSheet.Range(“A1”).Resize(UBound(VarArr)).Value = VarArr
    Set wksSheet = Nothing
End Sub

4 Comments Add yours

  1. keegi says:

    This row gives and error:

    For Each KK In VarArr

    Why is that?

  2. can you please post error description..
    what OS and VErsion are you using?

  3. Anthony Gough says:

    This is a good method that I often use to create unique arrays from any set of data which is essentially the same.

    sub uniqList()

    ‘where srtArr is an array containing duplicates
    Dim d As Object
    Set d = CreateObject(“Scripting.Dictionary”)
    Dim i As Long
    For i = LBound(srtArr) To UBound(srtArr)
    d(srtArr(i)) = 1
    Next i
    Dim v As Variant
    For Each v In d.Keys()
    Debug.Print v
    Next v
    End Sub

  4. Anthony Gough says:

    The previous reply can be deleted – I am new to the Groups and should not have posted this here. Please delete this and the previous please. Apologies

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s