Build comma delimited string

Question

I want to build a comma-delimited string from the range A1:A400 .

What is the best way to do this? Should I use a For loop?

Answer 1

The laziest way is

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

This works because the .Value property for a multi-network range returns a 2D array and Join expects a 1D array and Transpose tries to be too useful, so when it discovers a 2D array with only one column, it converts it to a 1D array .

In production it is recommended to use at least a slightly less lazy option

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

otherwise, the active sheet will always be used.

Answer 2

I would like to @GSerg answer the final answer to your question.

For completeness - and to solve several limitations in other answers, I would suggest using the "Join" function, which supports 2-dimensional arrays:

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

The point here is that the Value property of the range (if it is not a separate cell) is always a 2-dimensional array.

Please note that the line separator in the Join2d function Join2d present only when there are lines (plural) for delimitation: you will not see it in a concatenated line from a range with one line.

Join2d: 2-dimensional Join function in VBA with optimized string handling

Encoding Notes:

1. This Join function does not suffer from the 255-char restriction, which affects most (if not all) of the functions of the built-in cascade in Excel, and the Range.Value sample code above will be transmitted in the data, in full, from cells containing longer strings.
2. This is highly optimized: we use string concatenation as little as possible, since native VBA string concatenations are slower and slower as the 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 is the corresponding 2-D split function:

Split2d: 2-dimensional split function in VBA with optimized 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 code inserted into your browser (or using the StackOverflow formatting functions)

Answer 3

You can use the StringConcat function created by Chip Pearson. See link below :)

Topic: String Concatenation

Link : http://www.cpearson.com/Excel/StringConcatenation.aspx

Quote from link in case the link ever dies

This page describes the VBA function that you can use to concatenate string values ​​in an array formula.

StringConcat Function

To overcome these shortcomings of the CONCATENATE function, you need to build your own function written in VBA, which will solve the problems of CONCATENATE. The rest of this page describes a function called StringConcat. This function overcomes all the disadvantages of CONCATENATE. It can be used to concatenate individual string values, the values ​​of one or more ranges of worksheets, literal arrays, and the results of an array formula operation.

The declaration of the StringConcat function is as follows:

StringConcat Function (Sep As String, ParamArray Args ()) As String

The Sep parameter is a character or characters that separate related strings. It can be 0 or more characters. Sep is required. If you do not need separators in the result line, use an empty line for the value of Sep. The Sep value appears between each concatenation of the string, but does not appear at the beginning or at the end of the result string. The ParamArray Args parameter is a series of values ​​that must be combined. Each element in ParamArray can be any of the following:

A literal string such as "A", A range of cells specified either by address or by range name. When the elements of the two-dimensional range are concatenated, the concatenation order goes through one line and then down to the next line. Literal array. For example, {"A", "B", "C"} or {"A"; "B"; "C"}

Function

 Function StringConcat(Sep As String, ParamArray Args()) As Variant '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' StringConcat ' By Chip Pearson, [e-mail] , www.cpearson.com ' www.cpearson.com/Excel/stringconcatenation.aspx ' This function concatenates all the elements in the Args array, ' delimited by the Sep character, into a single string. This function ' can be used in an array formula. There is a VBA imposed limit that ' a string in a passed in array (eg, calling this function from ' an array formula in a worksheet cell) must be less than 256 characters. ' See the comments at STRING TOO LONG HANDLING for details. '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Dim S As String Dim N As Long Dim M As Long Dim R As Range Dim NumDims As Long Dim LB As Long Dim IsArrayAlloc As Boolean ''''''''''''''''''''''''''''''''''''''''''' ' If no parameters were passed in, return ' vbNullString. ''''''''''''''''''''''''''''''''''''''''''' If UBound(Args) - LBound(Args) + 1 = 0 Then StringConcat = vbNullString Exit Function End If For N = LBound(Args) To UBound(Args) '''''''''''''''''''''''''''''''''''''''''''''''' ' Loop through the Args '''''''''''''''''''''''''''''''''''''''''''''''' If IsObject(Args(N)) = True Then ''''''''''''''''''''''''''''''''''''' ' OBJECT ' If we have an object, ensure it ' it a Range. The Range object ' is the only type of object we'll ' work with. Anything else causes ' a #VALUE error. '''''''''''''''''''''''''''''''''''' If TypeOf Args(N) Is Excel.Range Then ''''''''''''''''''''''''''''''''''''''''' ' If it is a Range, loop through the ' cells and create append the elements ' to the string S. ''''''''''''''''''''''''''''''''''''''''' For Each R In Args(N).Cells If Len(R.Text) > 0 Then S = S & R.Text & Sep End If Next R Else ''''''''''''''''''''''''''''''''' ' Unsupported object type. Return ' a #VALUE error. ''''''''''''''''''''''''''''''''' StringConcat = CVErr(xlErrValue) Exit Function End If ElseIf IsArray(Args(N)) = True Then ''''''''''''''''''''''''''''''''''''' ' ARRAY ' If Args(N) is an array, ensure it ' is an allocated array. ''''''''''''''''''''''''''''''''''''' IsArrayAlloc = (Not IsError(LBound(Args(N))) And _ (LBound(Args(N)) <= UBound(Args(N)))) If IsArrayAlloc = True Then '''''''''''''''''''''''''''''''''''' ' The array is allocated. Determine ' the number of dimensions of the ' array. ''''''''''''''''''''''''''''''''''''' NumDims = 1 On Error Resume Next Err.Clear NumDims = 1 Do Until Err.Number <> 0 LB = LBound(Args(N), NumDims) If Err.Number = 0 Then NumDims = NumDims + 1 Else NumDims = NumDims - 1 End If Loop On Error GoTo 0 Err.Clear '''''''''''''''''''''''''''''''''' ' The array must have either ' one or two dimensions. Greater ' that two caues a #VALUE error. '''''''''''''''''''''''''''''''''' If NumDims > 2 Then StringConcat = CVErr(xlErrValue) Exit Function End If If NumDims = 1 Then For M = LBound(Args(N)) To UBound(Args(N)) If Args(N)(M) <> vbNullString Then S = S & Args(N)(M) & Sep End If Next M Else '''''''''''''''''''''''''''''''''''''''''''''''' ' STRING TOO LONG HANDLING ' Here, the error handler must be set to either ' On Error GoTo ContinueLoop ' or ' On Error GoTo ErrH ' If you use ErrH, then any error, including ' a string too long error, will cause the function ' to return #VALUE and quit. If you use ContinueLoop, ' the problematic value is ignored and not included ' in the result, and the result is the concatenation ' of all non-error values in the input. This code is ' used in the case that an input string is longer than ' 255 characters. '''''''''''''''''''''''''''''''''''''''''''''''' On Error GoTo ContinueLoop 'On Error GoTo ErrH Err.Clear For M = LBound(Args(N), 1) To UBound(Args(N), 1) If Args(N)(M, 1) <> vbNullString Then S = S & Args(N)(M, 1) & Sep End If Next M Err.Clear M = LBound(Args(N), 2) If Err.Number = 0 Then For M = LBound(Args(N), 2) To UBound(Args(N), 2) If Args(N)(M, 2) <> vbNullString Then S = S & Args(N)(M, 2) & Sep End If Next M End If On Error GoTo ErrH: End If Else If Args(N) <> vbNullString Then S = S & Args(N) & Sep End If End If Else On Error Resume Next If Args(N) <> vbNullString Then S = S & Args(N) & Sep End If On Error GoTo 0 End If ContinueLoop: Next N ''''''''''''''''''''''''''''' ' Remove the trailing Sep ''''''''''''''''''''''''''''' If Len(Sep) > 0 Then If Len(S) > 0 Then S = Left(S, Len(S) - Len(Sep)) End If End If StringConcat = S ''''''''''''''''''''''''''''' ' Success. Get out. ''''''''''''''''''''''''''''' Exit Function ErrH: ''''''''''''''''''''''''''''' ' Error. Return #VALUE ''''''''''''''''''''''''''''' StringConcat = CVErr(xlErrValue) End Function