vba get list without duplicates from listobject

Question

Is there a vba command to get directly a list without duplicates from the header autofilter of a list object.

My input is this list and I search a way to get this list without duplicates from my object ListObject in vba

Thanks in advance.

Possible duplicate https://stackoverflow.com/questions/31690814/how-do-i-get-a-list-of-unique-values-from-a-range-in-excel-vba — cSteusloff, Apr 25 '18 at 14:52
I have a non filtered list and would like to get a list without duplicates from my listobject — C.Romain, Apr 25 '18 at 15:06
Please read [*Why is “Can someone help me?” not an actual question?*](https://meta.stackoverflow.com/q/284236/1188513) — Mathieu Guindon, Apr 25 '18 at 15:20

score 0 · Accepted Answer · answered Apr 25 '18 at 16:56

you could use this helper function:

Function GetUniqueValues(rng As Range) As Variant
    Dim cell As Range

    With CreateObject("Scripting.Dictionary")
        For Each cell In rng
            .Item(cell.Value) = 1
        Next
        GetUniqueValues = .keys
    End With
End Function

to be called by your "main" module as follows:

Option Explicit

Sub main()
    Dim uniqueValues As Variant

    uniqueValues = GetUniqueValues(ActiveSheet.ListObjects(1).ListColumns(1).DataBodyRange)

    '... rest of your code
End Sub

just change ActiveSheet, ListObjects(1) and ListColumns(1) references as per your needs

Thanks a lot for your answer – C.Romain Apr 26 '18 at 09:26 — C.Romain, Apr 26 '18 at 09:26

vba get list without duplicates from listobject

1 Answers1