Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro question | Excel Worksheet Functions | |||
Pulling out data based on font colour | Excel Discussion (Misc queries) | |||
Inserting a new line when external data changes | Excel Discussion (Misc queries) | |||
Macro does not run when data refreshed | Excel Discussion (Misc queries) | |||
Daily Macro to Download Data, Order and paste in order | Excel Worksheet Functions |