Hi all,
I am looking to alter current coding in a concatenaterange function. See below:
Function ConcatenateRange(ByVal cell_range As Range, _
Optional ByVal seperator As String) As String
Dim cell As Range
Dim newString As String
Dim cellArray As Variant
Dim i As Long, j As Long
cellArray = cell_range.Value
For i = 1 To UBound(cellArray, 1)
For j = 1 To UBound(cellArray, 2)
If Len(cellArray(i, j)) <> 0 Then
newString = newString & (seperator & cellArray(i, j))
End If
Next
Next
If Len(newString) <> 0 Then
newString = Right$(newString, (Len(newString) - Len(seperator)))
End If
ConcatenateRange = newString
End Function
Within the workbook I use CHAR(10) as a separator however I want the function to count the total cells to be concatenated in the range and attribute a separator such as 1) Cell Detail 1, 2) Cell Detail 2, etc. For example if I had the following data in each cell:
A1: Thanks
A2: for the
A3: help!
I want it to look like:
B1: 1) Thanks
2) for the
3) help!
Thanks in advance!
- mbus
Bookmarks