Append string between each cell value in a series

Question

Let's say I have a series of cells like so:

    A
1  Foo
2  Bar
3  Hello
4  World
5  Random Text

What I'd like to do is have the result of my formula populate another cell with:

Foo, Bar, Hello, World, Random Text

Now, I know how to concatenate two cells with:

=A1&", "&A2

but how can I do the same thing with the entire series?

Answer 1

Here's a function you might be able to use. Simply put this in your workbook code module, then you can enter it in cells like:

=JoinRange(A1:A6) or =JoinRange(A2:D15), etc.

Public Function JoinRange(ByVal rng As Range) As String
    Dim dlmt As String: dlmt = ","
    Dim multiRow As Boolean: multiRow = rng.Rows.Count > 1
    Dim r As Long, c As Long

    Select Case rng.Columns.Count
        Case 1
            If multiRow Then
                JoinRange = Join(Application.WorksheetFunction.Transpose(rng), dlmt)
            Else:
                'a single cell
                JoinRange = rng
            End If
        Case Is > 1
            If multiRow Then
                'a 2d range of cells:
                For r = 1 To rng.Rows.Count
                    For c = 1 To rng.Columns.Count
                        JoinRange = JoinRange & rng(r, c) & dlmt
                    Next
                Next
                JoinRange = Left(JoinRange, Len(JoinRange) - 1)

            Else:
                JoinRange = Join(Application.WorksheetFunction.Transpose( _
                                Application.WorksheetFunction.Transpose(rng)), dlmt)
            End If
        Case Else

     End Select

End Function