Very Nice!
--
Gary''s Student - gsnu200905


"Rick Rothstein" wrote:

Assuming your cells contain text (as your post indicated they did) and not
formulas, give this code a try (set the DataStartCell and the
DestinationStartCell for your actual setup)....

Sub Concatter()
Dim X As Long, Off As Long, R As Range, LastCell As Range
Dim DataStartCell As Range, DestinationStartCell As Range
Set DataStartCell = Range("A1")
Set DestinationStartCell = Range("B1")
Set LastCell = Cells(Rows.Count, DataStartCell.Column).End(xlUp)
Set R = Range(DataStartCell, LastCell).SpecialCells(xlCellTypeConstants)
For X = 1 To R.Areas.Count
DestinationStartCell.Offset(Off).Value = _
Join(WorksheetFunction.Transpose(R.Areas(X)))
Off = Off + 1
Next
End Sub

--
Rick (MVP - Excel)


"andrei" wrote in message
...
I have in column A cells with the following content :

A1 : Mother
A2 : go
A3 : home
A4 : ( empty cell)
A5: ( empty cell)
A6 : Daddy
A7 : works
A8 : in a
A9 : mine
A10 : (empty cell)
A11 : My uncle
A12 : is a spy

What i want in a macro which does that

B1 : Mother go home
B2 : Daddy works in a mine
B3 : My uncle is a spy

The macro should concatenate the cells with text and "understand" empty
cells as delimiter .