VBA One-liner to Concatenate Excel Cell Values

Here's a VBA one-liner to concatenate a column of Excel cell values, with a separator, into one string: Join(Application.Transpose(<column range>), ","). The column range is an Nx1 array, Transpose() returns a one-dimensional N-element array and Join() function a one-dimensional array as the first parameter.

To concatenate values from a row of cells, you have to use Transpose() twice like this: Join(Application.Transpose(Application.Transpose(<row range>)), ","). Excel's Range always returns a two-dimensional array (in this case, a row is a 1xM array) and you flatten the two-dimensional array by calling Transpose() twice. There doesn't seem to be a built-in function to flatten two-dimensional arrays.

References

• The Join Function
• WorksheetFunction.Transpose Method