Dear all, multiple cell data to single cell

[nur2544]

Dear all, I need to copy multiple line content to single cell form “before” sheet to “after” sheet. Here data should be kept vertically in the single cell . Please don’t use warp text as it will be difficult for me to recognize the cell contents . Please check the attachments

Please Login or Register  to view this content.

sheet "after"

Please Login or Register  to view this content.

[mrice]

Try this

Please Login or Register  to view this content.

Open up the VBA editor by hitting ALT F11

Insert a new module by hitting Insert - Module

Paste the macro into the empty sheet

Hit ALT F11 to get back to the worksheet.

Run the macro by going to tools-macro in Excel 2003 or the view ribbon in Excel 2007/2010.

[AB33]

Try the attached.

[nur2544]

Hi Martin , yes it works perfectly , i appreciate your nice job . it is helping me . thanks for your valuable time..i am really happy.

[nur2544]

Hi AB33 , Thanks for your macro , i have tested your macro , here i am getting an error in the line(green color mark) , i think it happened due to cell B data as its data does not contains few character , it is something like
Temporary Issue/ Persistent issue : Persistent
Cust alternative/other number/MS : 733819

Please Login or Register  to view this content.

Run-time error'13':
Type mismatch

[jindon]

Try this and if you get any error, post back.

Please Login or Register  to view this content.

[nur2544]

Hi Jindon , Yes It works perfectly ! macro speed is awesome ! words are powerless to express my gratitude ! just thanks for your helping mind to others ..you are great ….

[jindon]

You are welcome and thanks for the feedback/rep point.

[AB33]

nur,
I know you have closed the thread as solved, but I do not see an error on my code with out seeing the error.

[nur2544]

Hi AB33, Sorry for late response, still I am getting error really , is it problem for Windos XP ? , but I am not sure why it happened , but here I observed on things that is : if I keep smaller range of data then it gives the results as well without having any error . but if I increase the data range like for example A2:B10000 , then it gives error as I said before in the post # 5 .
However , actually I have closed the issue as I thought that you will not work for my issue once again that is why I closed the issue , please I am sorry for that . but remember I always appreciate different type solution form others friend also as I am student of macro . thanks for understanding me .

[nur2544]

Hi Jindon , if you have time ! would you please explain the whole code in simple way , it is required for learning purpose . thanks

[AB33]

Okay!
It is your choice!
If you want me to adjust my code, I need to see in a sample why you are getting an error.

[AB33]

Nur,
Use Jindon's code.

[jindon]

Step through the code

Please Login or Register  to view this content.

[nur2544]

Hi AB33, Please check the attachments..my actual data range till 65536++

[AB33]

Nur,
I have done the test with 20k.
Yes, you are right. My code will not work. It is either the application.transpose can only accept up to certain size characters, or most likely the chr(10) can only take up to 1024 characters. I am not sure which one is, but I will do the research.

[nur2544]

Hi AB33, thanks you got my point..