how to convert this sub to a UDF

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

LVL 6
FloraAsked:
Who is Participating?
 
NorieConnect With a Mentor VBA ExpertCommented:
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

0
 
Martin LissOlder than dirtCommented:
What do you want to do with each row?
0
 
FloraAuthor Commented:
Perfect!  Thanks a million Norie
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.