How copy data form multi cells to one cell?

[B]

Hi,
I have problem with copping data form cells. I want to copy data from
multiple cells into one.
After pasting the data should be combined by char NewLine.
I tried to use function "text connections" connect.text (a1: A100) but
it does not work. Only work if i put (a1;a2;a3) but i have meny cells.

How do this?

 

[Gord Dibben]

You can do it using a formula with the concatenation operator as so.

=A1&Char(10)&A2&Char(10)

But that has its limits.

You could use a UDF like this to combine with linefeeds. Blank cells will be
ignored.

Function ConCatRange(CellBlock As Range) As String
Dim cell As Range
Dim sbuf As String
For Each cell In CellBlock
If Len(cell.Text) > 0 Then sbuf = sbuf & cell.Text & Chr(10)
Next
ConCatRange = Left(sbuf, Len(sbuf) - 2)
End Function

Usage: =ConCatRange(A1:A100)

Set the cell to wrap text but with 100 linefeeds you will not be able to autofit
the row height to accommodate that much data.


Gord Dibben MS Excel MVP