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) = ""
arrKeys = dict.Keys
res = Join(arrKeys, ",")
Sheets("Sheet1").Range("A1") = res
End Sub

Open in new window

Who is Participating?
NorieConnect With a Mentor VBA ExpertCommented:

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) = ""

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

End Function

Open in new window

Martin LissOlder than dirtCommented:
What do you want to do with each row?
FloraAuthor Commented:
Perfect!  Thanks a million Norie
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.