Arrange horizontal data to vertical through cut, paste, loop

**kcheeser** · 03-26-2014, 11:50 AM

I have some data that is arranged horizontally in rows that repeats the same type of data every 4 rows. It is repeated 12 times per row. I want to arrange the data vertically. So I want to identify the last row in the workbook, currently row 59 (although that will change everytime I add data). Select AW:59-AZ:59 and insert into E:60-H:60. Cut the next set of data: AS:59-AV:59 and insert into E:61-H:61. Etc. repeated until the data has been cut from I:59-L:59 and pasted into E:70-H70. Then I will need to loop the whole thing and do it in row 58, etc until I get to row 1. Row 1 contains headings and not data.

**Olly** · 03-26-2014, 11:54 AM

Please attach a workbook, showing clearly what your current structure looks like, and what your required outcome looks like. Please be clear about the required sort order of the final data, as there will be more than one way of looping through and transferring data to columns...

**kcheeser** · 03-26-2014, 12:14 PM

I have included a truncated form of the original. The number of rows will vary.
I also uploaded a form in which I did the cutting and pasting for the bottom two rows as an example of how I would like this to be done.

**Olly** · 03-26-2014, 12:30 PM

Are you really happy leaving A:D blank? Would it not be better to include the correct values in A:D for every row?

Is the order of the outcome essential (ie - reverse column order)? Because while it's possible to loop through the way you describe, it's probably more efficient to cut and paste larger blocks of text at a time.

**kcheeser** · 03-26-2014, 12:42 PM

Ideally, I would like for A:D to be filled with the data. It would be best as you suggest, but I didn't know if that was possible. The order of the columns is not important. However, the alignment of the data under the correct heading is imperative. For example. Data that begins in AW (column heading Word . . . ) will need to be pasted in column E (column heading Word . . .).

Data cut from row 2 will always need to have the associated Record ID, Exposure, Dose, and Dose Frequency as originally assigned. So any data from row 2 would need to have: Record ID=KAW007, Exposure=6, Dose=6, Dose Freq=1. However, the order of the columns or rows is not important as long as the data remains consistent with its original column heading, and original A:D entries.

**Olly** · 03-26-2014, 12:57 PM

Okay. Here's a loop of code that does exactly what you initially asked for, but a bit slowly:

Please Login or Register  to view this content.

Now here's another better solution, which works much faster, and includes data in A:D:

Please Login or Register  to view this content.

**kcheeser** · 03-26-2014, 01:25 PM

Thanks, but I get a run-time error:

Method 'Range' of object'_Worksheet' failed.

**Olly** · 03-26-2014, 01:56 PM

Originally Posted by kcheeser

Thanks, but I get a run-time error:

Method 'Range' of object'_Worksheet' failed.

Which subroutine, which line? Are you trying it on the workbook "original data"?

**kcheeser** · 03-26-2014, 01:58 PM

Last line of each sub. Yes, I tried it on the "original data".

**Olly** · 03-26-2014, 02:05 PM

Last line?

Please Login or Register  to view this content.

??

**kcheeser** · 03-26-2014, 02:09 PM

".Range(Cells(1, 9), Cells(1, lColMax)).Clear"

**Olly** · 03-26-2014, 02:16 PM

No idea. I can't replicate your error.

The code worked perfectly on my XL2010 machine when I wrote it this afternoon, and is working perfectly on my XL2013 machine now: Just downloaded your "original data" file, pasted the 'bar' code into a new module, and it ran perfectly.

**kcheeser** · 03-26-2014, 02:25 PM

Let me fiddle around with it and see what is going on. I'm also using 2013

**Olly** · 03-26-2014, 02:27 PM

There should be no fiddling needed. Apply that code to the workbook you uploaded, and it should work with no issues. Unless you've changed something.

**kcheeser** · 03-26-2014, 03:12 PM

I don't get it. I haven't changed a thing, and I can't get it to work. Still same error code.

**Olly** · 03-26-2014, 03:22 PM

Odd. I've asked if others can take a look, see if they get the same error and / or if I'm missing something!

**Solus Rankin** · 03-26-2014, 03:31 PM

Please upload the wb with the code already in place.

**protonLeah** · 03-26-2014, 03:38 PM

Both macros run w/2007

**kcheeser** · 03-26-2014, 03:39 PM

Hooray. I just tried it on a different computer and it worked beautifully. I will try to see what is going on with the settings on the other computer. Thanks SO much for your help.

**Olly** · 03-26-2014, 03:46 PM

Originally Posted by protonLeah

Both macros run w/2007

Thanks for testing

Glad it's working for you now, kcheeser.

Now, see if you can understand how the code actually works, so you can adapt it as and when necessary!

**kcheeser** · 03-26-2014, 03:49 PM

I will. I'm always learning. Thanks again.

**Izandol** · 03-26-2014, 04:04 PM

If Sheet1 is not active sheet that code will fail. It is better to write:

Please Login or Register  to view this content.

**Olly** · 03-26-2014, 04:12 PM

Originally Posted by Izandol

If Sheet1 is not active sheet that code will fail. It is better to write:

Please Login or Register  to view this content.

THAT'S what I was missing. Oops!

Thanks, Izandol.

Arrange horizontal data to vertical through cut, paste, loop

LinkBack

Thread Tools

Rate This Thread

Display

Arrange horizontal data to vertical through cut, paste, loop

Re: Arrange horizontal data to vertical through cut, paste, loop

Re: Arrange horizontal data to vertical through cut, paste, loop

Re: Arrange horizontal data to vertical through cut, paste, loop

Re: Arrange horizontal data to vertical through cut, paste, loop

Re: Arrange horizontal data to vertical through cut, paste, loop

Re: Arrange horizontal data to vertical through cut, paste, loop

Re: Arrange horizontal data to vertical through cut, paste, loop

Re: Arrange horizontal data to vertical through cut, paste, loop

Re: Arrange horizontal data to vertical through cut, paste, loop

Re: Arrange horizontal data to vertical through cut, paste, loop

Re: Arrange horizontal data to vertical through cut, paste, loop

Re: Arrange horizontal data to vertical through cut, paste, loop

Re: Arrange horizontal data to vertical through cut, paste, loop

Re: Arrange horizontal data to vertical through cut, paste, loop

Re: Arrange horizontal data to vertical through cut, paste, loop

Re: Arrange horizontal data to vertical through cut, paste, loop

Re: Arrange horizontal data to vertical through cut, paste, loop

Re: Arrange horizontal data to vertical through cut, paste, loop

Re: Arrange horizontal data to vertical through cut, paste, loop

Re: Arrange horizontal data to vertical through cut, paste, loop

Re: Arrange horizontal data to vertical through cut, paste, loop

Re: Arrange horizontal data to vertical through cut, paste, loop

Thread Information

Users Browsing this Thread

Similar Threads

How to arrange vertical Vcards column into horizontal rows ?

[SOLVED] Horizontal then Vertical Loop help

[SOLVED] how to arrange slicer look from vertical to horizontal?

Transpose horizontal data to vertical data with paste link

Arrange vertical string into multiple horizontal columns

Bookmarks

Bookmarks

Posting Permissions