CODEWITHSUNDEEP
excelvba
NZ_DJ
NZ_DJ

Reputation: 341

Concatenate Unique Values without counting blank cells

I am trying to concatenate unique values from a row. I found VBA code online but it counts blank cells as a unique value.

How can I alter the code so it doesn't count blank cells?

Function ConcatUniq(xRg As Range, xChar As String) As String
    Dim xCell As Range
    Dim xDic As Object
    Set xDic = CreateObject("Scripting.Dictionary")
    For Each xCell In xRg
        xDic(xCell.Value) = Empty
    Next
    ConcatUniq = Join$(xDic.Keys, xChar)
    Set xDic = Nothing
End Function

(Original code from ExtendOffice)

Upvotes: 1

Views: 266

Answers (1)

QHarr
QHarr

Reputation: 84465

You could use an If statement with IsEmpty

If Not IsEmpty(xCell) Then xDic(xCell.Value) = Empty as a test

You may wish to add a <> vbNullString comparison as well in case you want to exclude cells containing "".

If Not IsEmpty(xCell) And xCell.Value <> vbNullString Then xDic(xCell.Value) = Empty

Upvotes: 2

Related Questions