Does this work?

Function ConCatRange(CellBlock As Excel.Range) As String
Dim Cell As Excel.Range
Dim sbuf As String

For Each Cell In CellBlock
If Len(Cell.Text) Then
sbuf = sbuf & Cell.Text & Chr(166) & Cell.Text & "|"
End If
Next Cell
ConCatRange = Left$(sbuf, Len(sbuf) - 1)
End Function


On Jan 22, 7:09*pm, Excel Helps
wrote:
Could one of the kind people that helped me over the weekend advise me
whether the code below can be altered to give this result please?
data1¦data1|data2¦data2|data3¦data3
where data1 has been entered in one column,data2 in one column, data3 in one
column but the output from the code duplicates data1,2,and 3 and also puts
alternating broken and solid pipes inbetween.

Thank you

Function ConCatRange(CellBlock As Range) As String
Dim Cell As Range
Dim sbuf As String
Dim pipe As Boolean
pipe = True
* * For Each Cell In CellBlock
* * pipe = Not pipe

* * * * If Len(Cell.Text) 0 Then
* * * * * * If pipe = False Then
* * * * * * sbuf = sbuf & Cell.Text & Chr(166)
* * * * * * Else
* * * * * * sbuf = sbuf & Cell.Text & "|"
* * * * * * End If
* * * * End If
* * Next
* * ConCatRange = sbuf
End Function