Concatenate range
Brief description:
Concatenate all cells in range with an optional separator
Full description:
Returns a concatenated string of all cells in range with an optional argument to define a separation character.
Note: Excel has recently introduced a native function that accomplishes this task. However, it does not include the option of a separator so this function is not yet completely obsolete.
To use: Call the function from within Excel using the range selection tool to select the range to be concatenated.
Add a defined separating string if required:
Future feature to add: An option to skip blank cells and an option to move between column and row concatenation.
Code:
Function ConcatenateRange(SelectionRange As Range, Optional InsertStr As String) 'Concatenates a range of values ' Optional 2nd argument can be used to insert characters between each value ie comma, space etc Dim CurrCell As Range Dim TmpStr As String Dim CountCell As Integer TmpStr = "" CountCell = 0 For Each CurrCell In SelectionRange CountCell = CountCell + 1 If CountCell < SelectionRange.Count Then If CurrCell <> "" Then TmpStr = TmpStr & CurrCell & InsertStr Else If CurrCell <> "" Then TmpStr = TmpStr & CurrCell End If Next CurrCell ConcatenateRange = TmpStr End Function