Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Smart way to concatenate into B1 all cells below until new info in Column 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: 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
comparing a column of cell and then copy info to other cells | Excel Discussion (Misc queries) | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
COUNT NON-BLANK CELLS WITH REFERENCE TO ANOTHER COLUMN | Excel Worksheet Functions | |||
Count cells based on date range in another column | New Users to Excel | |||
Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions |