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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Smart way to concatenate into B1 all cells below until new info in Column A
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 Dennis wrote: 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 |
Reply |
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 |