Smart way to concatenate into B1 all cells below until new info in Column A

[Dennis]

Using 2003

Currently, I insert the following VBA formula which is entered manually
into B1. Note: In the case below, I am must change the formula by
adding &" "&OFFSET(Bx,y,0) for the number of cells in Column A (from
row 2 through row 7) restarting every time there is a new value (not
empty) in column A.

Hopefully, my columns line up close enough. I am entering this via
"Google Groups"

=B2&" "&OFFSET(B2,1,0)&" "&OFFSET(B2,2,0)&" "&OFFSET(B2,3,0)&"
"&OFFSET(B2,4,0)&" "&OFFSET(B2,5,0)&" "&OFFSET(B2,6,0)&"
"&OFFSET(B2,7,0)&" "&OFFSET(B2,8,0)&" "&OFFSET(B2,9,0
*********************************************************************************************
A B C D E

1
2 "Example 1" I The
3 love time
4 working is
5 in VBA 2:00 PM
6
7 daily
8 "Example 2"
Stop when the data ends

Note: like in Col B, there could be a blank cell in Col B
Note: I use B1 as a helper row (maybe I do not need to??)

When finished:


1 Example 1 I love working The time is 2:00PM
in VBA daily
2 Example 2 Stop when the data ends


Note: rows 1-my helper row, 3, 4, 5, 6, 7 & 9) have been
macro-deleted
Note: the change in data in Col A restarts the loop
Note: once the data in Col A stops, the macro should stop

The reason for the above, many use Excel as a note pad when they type
in values in a
column row after row as the cell fills up. Where if they used
Text-wrap they could place
the data in only one cell.

I would like the macro to do all for the used range starting at Row ??
(?? via Msg box) or for a selected range.

Thanks Dennis

 

[Dennis]

Extra point: I was not quite clear about when the macro should stop.
Previously, I mentioned when the data ends in column A. Well, there
could be data still in the other columns to the right to be processed.

What I should have said, the last row to be re-populated with the
concatenated data is the last row in column in Col A with data, BUT the
concatenating should continue in Col's B -> end of Cols used range and
down in rows from last row in column in Col A with data to the end of
the used range.

Note: quite often the new user in Excel will copy formating all the way
to 65536 and the "Used Range" is huge. Can I some how redefine Used
Range as Range with data?

Dennis