Merging Cell contents accross multiple rows

[Sugar Ape]

I am trying to write a macro that concatenates the content of an unknown number of rows in to a single cell and separates the values with a CHAR(10). The groups of data I want to concatenate are separated by a blank row so I imagine that the Macro will need to be terminated via a check for two blank rows on the trot or the presence of a terminating value (in my feeble attempts so far I have used the word 'end' as the terminator). Its complicated further by the fact that some of the cells that need to be merged contain date & time values so they will need to be converted to text.

Below is the skeleton of my initial attempts at getting my head around the algorithm but the main function of actually concatenating the cells is missing.

I have also attached an example worksheet that shows the original data and the desired output (ignore the cell merging, that’s just to make it easier to read).

I hope this makes some kind of sense... Thanks in advance for your help :-)

Stu

Please Login or Register  to view this content.

[DonkeyOte]

See if this works for you...

Please Login or Register  to view this content.

Goes without saying merging cells is generally a bad idea!

[Sugar Ape]

Absolutley brilliant. I cant understand most of the code, i suspect it may invlove voodo, but thank you very much indeed.
I take tyour point about merging cells, i have comented out the merge line in the code and it still works a treat.
Thanks again!
Stu

[DonkeyOte]

I'm glad it worked for you.

The code is not annotated so is perhaps confusing rather than being overly complicated.

In essence the code works along the lines of:

-- uses Special Cells to identify each "Block" of data in your overall range
-- iterates each block one by one
-- iterates each column within each block one by one
-- in the case of Column B it writes the contents of the column back into each cell again but as literal text strings rather than as true datetime stamps (to preserve formatting)
-- the contents of each column are then pushed into an Array
-- once the Array has been populated with all of the columns values the column range itself is cleared and then merged
-- post merge the Array contents are then joined together into one long string with Chr(10) splitting each entry and the results are written into the first cell.

It could no doubt be improved upon...

Please remember to mark your thread as solved.