Macro to concatenate into "B1" B2 thru B"x" based on new data in "Col A"
 

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: when all data in the Used-range** is processed, the macro
should stop


** (Note: quite often the new user in Excel will copy formating
all the way to 65536 and the "Used Range" is huge. How can I
redefine Used-Range as Range with real data not just formatting?)
************************************************** *************************************

The need for a VBA solution, is that many use Excel as a note pad when
they type in values in a column row after row as the cell fills up. If
they just
used "Text-wrap", they would have placed the all of the data in only
one cell.

I would like to have an option (MsgBox?) to start processing at Row ??
(?? via Msg box) or for the Used range below the start row.


Thanks Dennis