VBA Join equivalent for Collections

Question

How do I join a collection in VBA. In VB.net I usually join arrays using Join(..., "delimiter), but I cant figure out how to do this using collection in VBA.

Dim oColl As New Collection
Dim r As Range

Set r = ThisWorkbook.Sheets("Work1").Range("D13:D263")

For Each cell In r
     If IsEmpty(cell) Then

     Else
         oColl.Add ("a = ''" + cell.Text + "'' ")
     End If
Next

I need to now join all the collection values into one string

Answer 1

While you have solved your question you could speed up your code by

1. Using SpecialCells to work with the non-blanks rather than test each cell
2. Use a variant array rather than a range loop

The code below uses Join to merge the final strings together. If you didn't have the string manipulation (the "a = ''" + cell.Text + "'' " part) then you could simply join the specialcells range from step 1

Updated to handle multiple areas in the SpecialCells Collection and single cells (where varinats wont work"

Sub Diff()
Dim rng1 As Range
Dim rng2 As Range
Dim varTest
Dim strOut As String
Dim lngrow As Long
On Error Resume Next
With Range("D13:D263")
    Set rng1 = .SpecialCells(xlCellTypeConstants)
    If Not rng1 Is Nothing Then
        Set rng1 = Union(rng1, .SpecialCells(xlCellTypeFormulas))
    Else
        Set rng1 = .SpecialCells(xlCellTypeFormulas)
    End If
End With
On Error GoTo 0
If rng1 Is Nothing Then Exit Sub
For Each rng2 In rng1.Areas
    If rng2.Cells.Count > 1 Then
        varTest = Application.Transpose(rng2)
        For lngrow = 1 To UBound(varTest)
            varTest(lngrow) = "a = ''" & varTest(lngrow) & "'' "
        Next
        strOut = strOut & Join(varTest, ",")
    Else
        strOut = strOut & "a = ''" & rng2.Value & "'' "
    End If
Next
MsgBox strOut
End Sub