Combine Arrays

I have cells that have comma separated data that I need to combine into a single array and then remove duplicates.

For instance, D2 = "a,b,c", D3 = "b,c,d", D4 = "c,d,e" and I want my final array to show "b,c,d,e".

Due to other restrictions elsewhere in my code, I need to run a loop to add in the arrays instead of selecting them as a range (the row needs to meet certain criteria to be included).

Can anyone offer some direction on this? I did copy the functions offered at CPearson, but I am getting errors (http://www.cpearson.com/excel/VBAArrays.htm)

Thanks in advance.

If you are not Mac, this shoud do it.

Option Explicit

Sub test()
    Dim r As Range, e
    With CreateObject("Scripting.Dictionary")
        .CompareMode = 1
        For Each r In Range("d2", Range("d" & Rows.Count).End(xlUp))
            If r.Value <> "" Then
                For Each e In Split(r.Value, ",")
                    .Item(Trim$(e)) = Empty
                Next
            End If
        Next
        MsgBox Join$(.keys)
    End With
End Sub

These both worked well.

Thank you so much.

One further problem. It seems that, although my array now is gathering all of the information, I'm having a type mismatch error (run-time 13) when I'm trying to use it. The error appears at the line with "Unbound(arrStrings)"

The next piece of code uses each element of the array to match against a cell.

For i = 0 To UBound(arrStrings)
    Set MARKER = c1
    Do While c1.Value = MARKER.Value And c1.Offset(0, 1).Value = MARKER.Offset(0, 1).Value And c1.Offset(0, 2).Value = MARKER.Offset(0, 2).Value
        If Trim(MARKER.Offset(0, 13).Value) = Trim(arrStrings(i)) Then
            MsgBox ("blah, blah")
        End If
        Set MARKER = MARKER.Offset(1, 0)
    Loop
Next i

Anyone got an idea what might be causing this? Went to MSDN developer site, but the information only mentioned a service pack, which I already have.

Thanks again.

Originally Posted by kreitzig

It seems that, although my array now is gathering all of the information, I'm having a type mismatch error (run-time 13) when I'm trying to use it. The error appears at the line with "Unbound(arrStrings)"

Most probably arrStrins is not an Array...

Originally Posted by mike7952

Try this instead of using the join
xItem = .items

Perfect. Thanks much. It is working good, in fact the whole complicated mess is now working correctly.

Originally Posted by jindon

Most probably arrStrins is not an Array...

Yep, that's what i figured too. I am just a little lost because it's being loaded as an array...but I guess that's the problem.