Need help transposing Rows into 3 equal columns

[Onenguyen]

Hello everyone. I am new to this site and I have been reviewing some great post from the members and admins here. I hope you're able to help me with my problem.

I have created a batch file that automatically creates a directory listing of any folder that I choose. I have made it so that it saves the listing as a csv and opens the file in excel.

I am getting stuck because I need to be able to break that list down into 3 equal columns.

Ex:

a
b
c
d
e
f

into: a b c
d e f

I have tried to transpose but it spreads the list completely over the total amount of columns. Is there anyway to limit the range to 3 columns?

Thank you again for your help!

[MickG]

Hi, Try this:-

Please Login or Register  to view this content.

Regards Mick

[Onenguyen]

Hi, Try this:-

Please Login or Register  to view this content.

Regards Mick

Micky! You're amazing. Is there anyway to make it so the first column gets deleted and the transposed columns shift left? Thank you so much!

[Onenguyen]

I also have a question that most of you will think is pretty dumb, but when I create the macro and insert the script it only saves it in the current worksheet. Is there anyway to save it so I can use this macro for future worksheets that I create in the future?

I know when you use the "Record New Macro" it gives you an option to save to your Personal folder, but it doesn't give me that option when creating one via VBA.

Thanks again!

[MickG]

Hi, If deleting Column "A" will do, then add this line as below/

Please Login or Register  to view this content.

Ps:- If you save your code in an Odinary Module (Not sheet) then you can call it from any sheet , in case below using a CommadButton in any sheet.

Please Login or Register  to view this content.

Mick

[Marcol]

For what its worth
Here is a formula based method

In C1 put the number of rows you need to transpose
D1

Please Login or Register  to view this content.

Drag across until Value =0


In C2

Please Login or Register  to view this content.

Drag/Fill Down and Across

Change the value in C1 to see the results

[Onenguyen]

Sorry I think I meant to say that I want to use this macro for any further workbooks. I am constantly creating new workbooks for different jobs, but the output always needs to be in the same format (I'm given a list and need it to be formatted into 3 columns). With word I am able to save the macro and pull it up for whatever document I need. I hope there's a way to do that with excel.

Thanks again. Your code worked perfectly!