How to move every 11th column in row A2 to a new row

**bmmcky** · 05-07-2013, 02:12 PM

Hi,

I have a data range of A2 to AQB2 and I would like to move every 11th column starting with K2 to the next row (so K2 to A3, U2 to A4, AE2 to A5, etc) -- or in other words keeping 10 columns per row.

I've tried to copy the codes of other threads but cannot fill in the gaps as I am unfamiliar with excel code and just know the basics.

If someone could help me out it would be very much appreciated!

Thanks,

Ben

**arlu1201** · 05-07-2013, 02:31 PM

Do you want only the 11th column to be moved or from the 11th column to the 20th column, 21st to 30th column, etc?

**bmmcky** · 05-07-2013, 02:42 PM

Oh yes, sorry -- from the 11th to the 20th; 21st to 30th etc.

Thanks!

**arlu1201** · 05-07-2013, 02:50 PM

Try this code - it will take any number of columns till XFD (end of file).

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

**bmmcky** · 05-07-2013, 03:03 PM

Close! It must have to be tweaked just a bit -- it took the last set of 10 columns at the very end and moved them to A2 instead of moving Columns K-T to A3; U-AD to A4 and so forth.

Am I explaining that clearly enough?
Thanks for the help!

**arlu1201** · 05-07-2013, 03:16 PM

Do you have any blank cells in between the data which then gets copied to column A?

The macro determines the last row of data by checking column A. So if you have blank cells, the copying can go hay-wire.

**bmmcky** · 05-07-2013, 03:55 PM

Ah yes, exactly. I replaced all blank cells with XXX and the code worked perfectly! Thanks so much for your help! Very much appreciated!

Cheers!

**arlu1201** · 05-08-2013, 01:29 AM

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.