(How to) Transpose one long list of numbers in CSV file to a number of rows

Hi Everyone,

In the past, everyone here has been very helpful to a VBA beginner like me, so I thought I'd see if someone can help me again!

I have one long list of 3,500,000 numbers in column A of a csv file. I would like to write a macro transposing these numbers 700 at a time, into 5,000 rows of 700 numbers.

Any suggested codes or help would be greatly appreciated!

Thanks!!

I originally started with a Microsoft Access Table of my data. I then created a query and exported it as a csv.

I was under the impression that from this csv, I could create a macro modifying the data into the shape I wanted - 5,000 rows x 700 columns. From there, I have a workbook that will take that csv file and parse it into 3 worksheets - the first 256 columns, second 256 columns, and remaining 188 columns. Ultimately, I only want the worksheet with the first 256 columns. With that said, is there a better way to do this?

arlu1201 - thank you very much for your suggested coding! However, I get a run-time error '1004' saying that my paste won't fit on the worksheet. So, I then tried modifying the code to pick up the first 250 numbers. But, then it seems to transpose the first row and then stop. Unfortunately I am not savvy enough in VBA to fix it from there. Do you have any suggestions?

Thanks again!!

Hi event,

Thanks a lot for the suggestion!

The 3 minute coding worked just fine. However, I apologize for not being very clear with what I was hoping to accomplish.

Your coding appears to output the first 5,000 #'s into column A, then the next 5,000 #'s into column B, and so on for 700 columns.

However, I was hoping to have the first 700 #'s output into row 1, then the next 700#'s output into row 2, and so on for 5,000 rows.

Is that an easy tweak? I tried playing around with the coding, but seem to be missing something.

Hi -

Try;

Please Login or Register  to view this content.

Regards,
event

Thank you so much for the help, event! I really appreciate it =)

This time it seems to take the first 256 #s and put them into row one of sheet1, the next 256 #s and put them into row two of sheet1, etc. until the end of sheet1, and then move onto sheet2.

However, I was hoping to have the first 256 #s put into row one of sheet1, then the next 256 #s put into row one of sheet2, and the final 188 #s put into row one of sheet3, etc. for 5,000 rows spreading over 3 sheets.

Or, since I don't really need sheets 2 and 3, all I really want is the first 256 #s on row 1, #s 701-956 on row 2, #s 1401-1656 on row 3, etc for 5,000 rows on one sheet.

I hope that makes sense. Any suggestions with that in mind?

Thanks again!!

Hi -

Try;

Please Login or Register  to view this content.

Your last explanation makes sense now.

Regards,
event

Thanks again for the reply, event!

This time I see to be getting a run-time error '9' - subscript out of range in the line

Please Login or Register  to view this content.

Hi -

Did you know how to step/debug the code?

Regards,
event

Let me give it a shot.

To be honest, my experience with VBA has primarily been in the form of taking pre-written macros and modifying them for my specific need. However, they are usually much simpler than yours =)

Anyway, thanks for the help up to this point!

Just wanted to let you know that I was able to figure it out. Thanks SO much event!!

Hi -

Great, could you please post your final solution for every one's with same issues?

Regards,
event

Sorry for the delayed reply. I have been out of the office and just came back.

Yes, your final suggestion:

Please Login or Register  to view this content.

worked for me. I hadn't realized that at the very bottom of my csv data feed, there was 1 extra line, causing the error 9.

Apologize for the mistake of mine! Thanks again for all the help - greatly appreciated =)