Build a Comma Delimited String

I want to build a comma delimited string from Range A1:A400.

What is the best way of doing this? Should I use a For loop?

Solution 1:

The laziest way is

s = join(Application.WorksheetFunction.Transpose([a1:a400]), ",")

This works because .Value property of a multicell range returns a 2D array, and Join expects 1D array, and Transpose is trying to be too helpful, so when it detects a 2D array with just one column, it converts it to a 1D array.

In production it is advised to use at least a little bit less lazy option,

s = join(Application.WorksheetFunction.Transpose(Worksheets(someIndex).Range("A1:A400").Value), ",")

otherwise the active sheet will always be used.

Solution 2:

I would regard @GSerg's answer as the definitive reply to your question.

For completeness - and to address a few limitations in other answers - I would suggest that you use a 'Join' function that supports 2-Dimensional arrays:

s = Join2d(Worksheets(someIndex).Range("A1:A400").Value)

The point here is that the Value property of a range (providing it isn't a single cell) is always a 2-Dimensional array.

Note that the row delimiter in the Join2d function below is only present when there are Rows (plural) to delimit: you won't see it in the concatenated string from a single-row range.

Join2d: A 2-Dimensional Join function in VBA with optimised string-handling

Coding notes:

1. This Join function does not suffer from the 255-char limitation that affects most (if not all) of the native Concatenate functions in Excel, and the Range.Value code sample above will pass in the data, in full, from cells containing longer strings.
2. This is heavily optimised: we use string-concatenation as little as possible, as the native VBA string-concatenations are slow and get progressively slower as a longer string is concatenated.

    Public Function Join2d(ByRef InputArray As Variant, _ 
                           Optional RowDelimiter As String = vbCr, _ 
                           Optional FieldDelimiter = vbTab,_ 
                           Optional SkipBlankRows As Boolean = False) As String

' Join up a 2-dimensional array into a string. Works like VBA.Strings.Join, for a 2-dimensional array.
' Note that the default delimiters are those inserted into the string returned by ADODB.Recordset.GetString
On Error Resume Next

' Coding note: we're not doing any string-handling in VBA.Strings - allocating, deallocating and (especially!) concatenating are SLOW.
' We're using the VBA Join & Split functions ONLY. The VBA Join, Split, & Replace functions are linked directly to fast (by VBA standards)
' functions in the native Windows code. Feel free to optimise further by declaring and using the Kernel string functions if you want to.

' **** THIS CODE IS IN THE PUBLIC DOMAIN ****   Nigel Heffernan   Excellerando.Blogspot.com

Dim i As Long
Dim j As Long
Dim i_lBound As Long
Dim i_uBound As Long
Dim j_lBound As Long
Dim j_uBound As Long
Dim arrTemp1() As String
Dim arrTemp2() As String
Dim strBlankRow As String

i_lBound = LBound(InputArray, 1)
i_uBound = UBound(InputArray, 1)
j_lBound = LBound(InputArray, 2)
j_uBound = UBound(InputArray, 2)

ReDim arrTemp1(i_lBound To i_uBound)
ReDim arrTemp2(j_lBound To j_uBound)

For i = i_lBound To i_uBound

    For j = j_lBound To j_uBound
        arrTemp2(j) = InputArray(i, j)
    Next j
    arrTemp1(i) = Join(arrTemp2, FieldDelimiter)
Next i

If SkipBlankRows Then
    If Len(FieldDelimiter) = 1 Then
        strBlankRow = String(j_uBound - j_lBound, FieldDelimiter)
    Else
        For j = j_lBound To j_uBound
            strBlankRow = strBlankRow & FieldDelimiter
        Next j
    End If

    Join2d = Replace(Join(arrTemp1, RowDelimiter), strBlankRow & RowDelimiter, "")
    i = Len(strBlankRow & RowDelimiter)

    If Left(Join2d, i) = strBlankRow & RowDelimiter Then
        Mid$(Join2d, 1, i) = ""
    End If 
Else
    Join2d = Join(arrTemp1, RowDelimiter)
End If
Erase arrTemp1
End Function

For completeness, here's the corresponding 2-D Split function:

Split2d: A 2-Dimensional Split function in VBA with optimised string-handling

Public Function Split2d(ByRef strInput As String, _ 
                        Optional RowDelimiter As String = vbCr, _ 
                        Optional FieldDelimiter = vbTab, _ 
                        Optional CoerceLowerBound As Long = 0) As Variant

' Split up a string into a 2-dimensional array. Works like VBA.Strings.Split, for a 2-dimensional array.
' Check your lower bounds on return: never assume that any array in VBA is zero-based, even if you've set Option Base 0
' If in doubt, coerce the lower bounds to 0 or 1 by setting CoerceLowerBound
' Note that the default delimiters are those inserted into the string returned by ADODB.Recordset.GetString
On Error Resume Next

' Coding note: we're not doing any string-handling in VBA.Strings - allocating, deallocating and (especially!) concatenating are SLOW.
' We're using the VBA Join & Split functions ONLY. The VBA Join, Split, & Replace functions are linked directly to fast (by VBA standards)
' functions in the native Windows code. Feel free to optimise further by declaring and using the Kernel string functions if you want to.


' **** THIS CODE IS IN THE PUBLIC DOMAIN ****   Nigel Heffernan  Excellerando.Blogspot.com

Dim i   As Long
Dim j   As Long
Dim i_n As Long
Dim j_n As Long
Dim i_lBound As Long
Dim i_uBound As Long
Dim j_lBound As Long
Dim j_uBound As Long
Dim arrTemp1 As Variant
Dim arrTemp2 As Variant

arrTemp1 = Split(strInput, RowDelimiter)

i_lBound = LBound(arrTemp1)
i_uBound = UBound(arrTemp1)

If VBA.LenB(arrTemp1(i_uBound)) <= 0 Then  ' clip out empty last row: common artifact data loaded from files with a terminating row delimiter
    i_uBound = i_uBound - 1
End If

i = i_lBound
arrTemp2 = Split(arrTemp1(i), FieldDelimiter)

j_lBound = LBound(arrTemp2)
j_uBound = UBound(arrTemp2)

If VBA.LenB(arrTemp2(j_uBound)) <= 0 Then  ' ! potential error: first row with an empty last field...
    j_uBound = j_uBound - 1
End If

i_n = CoerceLowerBound - i_lBound
j_n = CoerceLowerBound - j_lBound

ReDim arrData(i_lBound + i_n To i_uBound + i_n, j_lBound + j_n To j_uBound + j_n)

' As we've got the first row already... populate it here, and start the main loop from lbound+1

For j = j_lBound To j_uBound
    arrData(i_lBound + i_n, j + j_n) = arrTemp2(j)
Next j

For i = i_lBound + 1 To i_uBound Step 1
    arrTemp2 = Split(arrTemp1(i), FieldDelimiter)   
    For j = j_lBound To j_uBound Step 1    
        arrData(i + i_n, j + j_n) = arrTemp2(j)    
    Next j    
    Erase arrTemp2
Next i

Erase arrTemp1

Application.StatusBar = False

Split2d = arrData
End Function

Share and enjoy... And watch out for unwanted line breaks in the code, inserted by your browser (or by StackOverflow's helpful formatting functions)