need help to copy paste the data from multiple columns into single row.

[apowale]

Hi,
Require assistance for following:
The data available is set of rows (for eg. A1: A20) with some blank rows in between.
Like this the data is present in multiple columns. The range of data is A1:HIR20 (5660 columns)

I want to bring the data into single row, like following
A1:A20 (as is)

The next column i.e. B1:B20 should be cut/paste into 2 rows below, which is A52.
Hence the data will get stored as A22:A41

The next column i.e. C1:C20 should be cut/paste into 2 rows below, which is A43.
Hence the data will get stored as A43:A62 and so on.
The sample excel file with desired output is attached for ready reference.
Please advise,

Thanking you,
Regards,
Amol

[nilem]

Hi Amol,
maybe so

Please Login or Register  to view this content.

[arlu1201]

Try this code -

Please Login or Register  to view this content.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button

[brytboy2]

Save your workbook. If this code doesn't do what you want, the only way to go back is to close without saving and reopen.

Select the data you want to list in one column. Must be contiguous columns. May contain blank cells.

Press Alt+F11 to open the VBE

Press Control+R to view the Project Explorer

Navigate to the project for your workbook and choose Insert - Module

Paste this code in the code pane

Please Login or Register  to view this content.

Press F5 to run the code

[arlu1201]

brytboy2,

Welcome to the forum.

I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. If you need more information on how to use them, check my signature below this post.

[apowale]

Dear Nilem, Arlette,

The script worked like a *Magic* I have wasted many hours in doing this task.
Really thanks, you made my day...

and I really appreciate for your prompt response.

Regards,
Amol

[arlu1201]

Hope you tried the other 2 solutions too

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.

[apowale]

Hi Arlette,

I tried yours and other 2 solution too.
It worked superb. M really very happy to see the prompt response from the Forum members.

Thank you all for your kind support.

@ Arlette : Whr do i get to learn the VB basics, M very much interested in learning and helping others.
@ brtyboy2 : I am unable to get output of your script, I may not be executing it properly. Anyways thanks for response
@ Nilem : Gr8 !!!

Regards,
Amol Powale.

[arlu1201]

There is a sticky thread - http://www.excelforum.com/excel-prog...materials.html which contains links to many learning materials.