Create list of unique values from a range in Excel

Question

I have data (names) spread out in a sheet in Excel. It is in columns A through M. Some rows have data in every column (A-M), some rows have nothing.

I want to extract a list of every unique value (or even a list of every value, and then I can remove duplicates) in the range.

For the life of me, I cannot figure out how to do this and haven't found an example online that pulls unique values from a RANGE, and not a single column.

Any ideas? Let me know if you need a screenshot of the data for more context.

Answer 1

Give this macro a try:

Sub MAINevent()
    Dim it As Range, r As Range, x0
        With CreateObject("scripting.dictionary")
            For Each it In Range("A:M").SpecialCells(2)
                x0 = .Item(it.Value)
            Next

            Set r = Cells(1, "N").Resize(.Count, 1)
            r.Value = Application.Transpose(.Keys)
        End With
End Sub