how to convert this sub to a UDF

Flora Edwards
Flora Edwards used Ask the Experts™
on
I had this question after viewing Formula to extract unique string from sheet2 column C.


how to convert this code into UDF

Sub macro()
Set ws2 = Sheets("Sheet2")
lr = ws2.Cells(Rows.Count, 3).End(xlUp).Row

Set Rng = ws2.Range(Range("C2"), ws2.Range("C" & Rows.Count).End(xlUp))
Set dict = CreateObject("Scripting.Dictionary")

For Each c In Rng
    dict(c.Value) = ""
Next
arrKeys = dict.Keys
res = Join(arrKeys, ",")
Sheets("Sheet1").Range("A1") = res
End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
What do you want to do with each row?
Analyst Assistant
Commented:
Flora

Perhaps something like this which you would use like this.

=GetUniqueList(range, delimiter)

Function GetUniqueList(rng As Range, Optional Delim = ",")
Dim dict As Object
Dim arrKeys As Variant
Dim c As Range

    Set dict = CreateObject("Scripting.Dictionary")

    For Each c In rng
        dict(c.Value) = ""
    Next

    arrKeys = dict.Keys
    
    GetUniqueList = Join(arrKeys, Delim)

End Function

Open in new window

Flora EdwardsMedicine

Author

Commented:
Perfect!  Thanks a million Norie

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial