Transpose Help; I need to make every 4 rows into one row with 4 columns! Please!

I have a Home business with a list of over 1000 rows. Every 4 rows is the information for one transaction. I need to take every 4 rows and make them one row with 4 columns.

Example: this is what I have;

A1(name1)
A2(date)
A3(product)
A4(price)
A5(name2)
A6(date)
A7(product)
A8(price)
ECT..



This is what I need;

A1 B1 C1 D1
(name1)(date) (product) (price)

A2 B2 C2 D2
(name2)(date)(product)(price)

I did it with highlighting the first 4 rows in column A, hit copy, highlighted the first 4 columns in row 1, and hit paste special/ transpose.

But that way only works manually one transaction at a time. I have about 1400 on this sheet, and a few more sheets to do. I need a way to apply that the entire page. Someone please help.!

Maybe:

Please Login or Register  to view this content.

@ JOHN H. DAVIS: I tried your code but I'm getting the rows in this order in the columns: 2, 3, 4, 1.

Anyway, here's my potential solution:

Please Login or Register  to view this content.

@ walruseggman - Of course, I didn't read the thread carefully and assumed there was a header.

Thank you all so so so much. I have been trying to figure this out literally for a week and could not find anything similar to what I needed.

At first in A column, I labeled every row 1,2,3,4,1,2,3,4,1,2,3,4 ect, and was trying to find a way to search entire sheet and every row that had a certain number I was trying to get it just moved right to a specific column, then I was gonna erase all blank cells and shift them up so that it would give me these same results. Then the more I searched and looked around, I found this to be what I believe the simplest method.

Dumb question..... But where do I input the codes at and make it do its thing? The Macro button then new? Or....



Thanks again so so much. I hope this works you have no idea how important this is to me i get these documents taken care of!

Open up the VBA editor (Alt + F11). In the project viewer on the Left side of the VBA editor, find your workbook, then double click the sheet object that you want the code to run on (Sheet1 or whatever). Paste the code in the white space to the right, then with your cursor inside the code, hit the play button in the tool bar, or press f5.

Norie, the one you said to try had a runtime - 9 error or something... Im trying another one it started working, Excel "not responding" at first but I think it went thru, ill check it...

I got the one by walruseggman to work. It's perfect! I started at the bottom and tried the last one first... It was the runtime error, it said something was invalid I forget what tho.

I'm on my cell typing on here, when I needed to use the code I sent the text from my phone to computer to paste it.

How would I delete the extra empty rows at the bottom? There's about 1500 of them.

I do have another list to do. I haven't opened it yet so I'm not sure if it's set up the same 4 line format... Let me see.

Glad my code worked for you. Any reputation points (the * in the lower left of my posts) is appreciated.

How would I delete the extra empty rows at the bottom? There's about 1500 of them.

Well the basic syntax for that is below (where the rows you want deleted go in the parenthesis), but but how are the cells you want deleted identified? i.e. how would the code know what rows you want deleted?

Please Login or Register  to view this content.

Reps added! Thank you. I was just wanting to DELETE all the empty rows after the last row I have text in. Example, say I have 1500 rows with data, and blank rows all the way upto 2000, I want to delete rows 1501-2000. To make the page not as long....

I also have another sheet, with a very similar set up. I currently have a bunch of rows, like 1000 or so, and randomly I'm there I have some blank rows. I tried to goto special: blanks: delete & shift cells up, but they won't go away. It has worked before but for some reason won't now.

Then once I get that, I can actually apply your first code to this page as well and it will set that data up correctly too. Then my work SHOULD be done!