macro excel merge data from multiple cell rows into single cell

[corescript]

hi brothers how to merge data from multiple cell rows into single cell using macro in excel
here i attach workbook to make clear what i want

thank you

[oeldere]

I'm interested in the purpose (use) of this action.

15 A.P. MÖLLER - MAERSK A/S A.P. MOLLER FINANCE SA

For this item you get such a long line that:
1) it not easy to read
2) maybe it is not possible to put it in 1 line (max of char reached for excel).

[Max, Singapore]

In a spare copy of your file, try this method which uses a sub (credits to Mike H) ..

In your Before sheet,
Do a "fill-down-from-above" first to fully populate col B (this is a prerequisite)
Put in D2: =B2
Put in D3: =IF(B3="",D2,B3)
Copy D3 down to last row of data in col B
Then cut col D and paste special as values to overwrite col B
(Note that the new col B is now fully populated)

Now run the sub below to generate the results in cols D and E

Please Login or Register  to view this content.

Note: I'm not sure, but you might have a truncation problem in Excel 2003 with this guy: A.P. MÖLLER - MAERSK A/S
as there's a whole lot of associated data to squeeze into a single cell
(I tried it in Excel 2007, which I believe has a higher limit per cell)

------
Success? celebrate it, click the star at the bottom left of my response

[corescript]

ok guys thank for respond i'll try it

[corescript]

I'm interested in the purpose (use) of this action.

15 A.P. MÖLLER - MAERSK A/S A.P. MOLLER FINANCE SA

For this item you get such a long line that:
1) it not easy to read
2) maybe it is not possible to put it in 1 line (max of char reached for excel).

hi what is the code if i want to delete the rest if the character more than 30000