Build a Comma Delimited String

by

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.

More Related Contents:

  1. How to find numbers from a string?
  2. Matching similar but not exact text strings in Excel VBA projects
  3. Excel VBA like operator [closed]
  4. How to extract file name from path?
  5. Check if a string contains another string
  6. Split string into array of characters?
  7. Call a Sub with a String
  8. Excel VBA: Range to String Array in 1 step
  9. Any method equivalent to PadLeft/PadRight?
  10. Turn Excel range into VBA string
  11. Unicode string literals in VBA
  12. Implementing String.Format() in VB6
  13. Excel: Use formula longer that 255 characters
  14. How to use sed/grep to extract text between two words?
  15. Convert string to a variable name
  16. How do I use variables in single quoted strings?
  17. How to match a String against string literals?
  18. Quicker way to get all unique values of a column in VBA?
  19. Use of symbol # (hash) in VBA Macro
  20. How can I concatenate multiple optional strings in swift 3.0?
  21. Excel VBA looping through multiple worksheets
  22. When one should use Set [e.g for SpecialCells return value]?
  23. Concatenate cell data into another data if values matches
  24. How to convert “Index” to type “Int” in Swift?
  25. Outputting Excel rows to a series of text files
  26. In Excel VBA on Windows, for parsed JSON variables what is this JScriptTypeInfo anyway?
  27. XML parse VBA excel (function trip, & MSXML2.DOMDocument)
  28. How to copy a chart from Excel to PowerPoint?
  29. How to split a string in Haskell?
  30. EXCEL VBA UserInterfaceOnly:= True not working

Leave a Comment