VBA: Search column for first instance of string and assign to new array or string

Question

I have a column that contains mixed strings and I need to find all the unique strings and declare to either a string or array variable. The last row of the column will vary so I cannot use a definite range. I was thinking of using some form of string comparison of the preceding cell and current cell, but like I said the data is mixed so when redundant data comes up this complicates the problem. Here is a picture to try and explain it better. EDIT: The string concatenation I will worry about that later.

Gary's Student · Accepted Answer · 2020-08-06 21:36:31Z

1

Using Excel 365.

With data in A1 through A11, in another cell enter:

=UNIQUE(A1:A11)

to get:

or:

=TEXTJOIN(",",TRUE,UNIQUE(A1:A11))

to get a comma-separated list:

EDIT#1:

With VBA, try this UDF:

Public Function unikue(rng As Range)
    Dim arr, c As Collection, r As Range
    Dim nCall As Long, nColl As Long
    Dim i As Long
    Set c = New Collection
    
    nCall = Application.Caller.Count
    
    On Error Resume Next
        For Each r In rng
            c.Add r.Text, CStr(r.Text)
        Next r
    On Error GoTo 0
    nColl = c.Count
    
    
    If nCall > nColl Then
        ReDim arr(1 To nCall, 1 To 1)
        For i = 1 To nCall
            arr(i, 1) = ""
        Next i
    Else
        ReDim arr(1 To nColl, 1 To 1)
    End If
    
    For i = 1 To nColl
        arr(i, 1) = c.Item(i)
    Next i
    
    unikue = arr
End Function

edited Aug 6, 2020 at 21:36

answered Aug 6, 2020 at 21:18

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gary's Student Over a year ago

@ian.templeton see my EDIT#1

T.M. Over a year ago

No problem doing this in VBA as the above Excel formula result can be written to any cell using VBA via Evaluate() (not forgetting double quotations marks within the string to be evaluated:-)

Collectives™ on Stack Overflow

VBA: Search column for first instance of string and assign to new array or string

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related