Macro to transpose columns

Microsoft Office excel 2007 home & stude...
June 8, 2010 at 10:31:24
Specs: Windows 7
Hello,

I am new to VBA and need to create a macro
that automatically transposes many rows of
data onto one column. Data looks like this:

123456789
259979456
879841268

And I want the data to be stacked up on one
column. For example:

1
2
3
4
5
6
7
8
9
2
5
9
9
etc.

Right now the quickest I can do this is by
manually copying a row's data and doing a
Copy-Paste Special-Transpose, then going to
the next row and repeating. Can anyone help
me with a macro that copies a row,
transposes the data, goes down to the next
row, copies, repeats (loops)?

Thank you very much.


See More: Macro to transpose columns

Report •

#1
June 8, 2010 at 11:09:14
Are your digits in individual cells or is 123456789 all in a single cell?

In what cell(s) do they start and where do you want them transposed to?


Report •

#2
June 8, 2010 at 11:14:35
Sorry for the confusion, yes, they are all in different cells. So
for example:

A1:E1 -- 38274
A2:E2 -- 67895
A3:E3 -- 15937

And I would want them to appear on any blank column like
this:
G1:G15 --
3
8
2
7
4
6
7
8
9
5
1
5
9
3
7

I have thousands of rows of data, so a loop macro is definitely
needed.


Report •

#3
June 8, 2010 at 12:50:29
I have thousands of rows of data, so a loop macro is definitely needed.

Well, not definitely...

It can be done with a formula.

Assuming your data is in Sheet1, put this in A1 of any other sheet:

=INDEX(Sheet1!$A$1:$E$4,1+INT(ROW()/5.1),ROW()-INT(ROW()/5.1)*5)

Change Sheet1!$A$1:$E$4 to refer to the actual range of your "thousands of rows".

Change the 5's to match the number of columns in your table.

In other words, your example uses column A:E, so I used 5. If your data is in A:G, use 7's instead of 5's. Leave the ".1", just change the 5's. All three of them.

Drag the formula down as far as you need.


Report •

Related Solutions

#4
June 8, 2010 at 13:09:36
I tried it out and it partially worked. I was getting the values
requested up until the 67th value when I got a #REF! error.
Thereafter I would have 5 correct values, then another REF error.
Farther down I am getting two consecutive REF errors, three
REF errors farther down, etc. The numbers in the data source
are all in the same format and I changed the 5s to 6s to get the
right column amount so I don't know what the issue is. The entire
data range was changed as well.

Report •

#5
June 8, 2010 at 16:23:50
Sorry about that. I tested it on a small range and didn't realize that the math within the formula gets a little funky as the number of rows increases.

I tried this on a range that was 10,000 rows by 10 columns and it transposed all the data until it filled 65,536 cells (Excel 2003)

=INDEX(Sheet1!$A$1:$J$10000,1+INT(ROW()/10.0001),ROW()-INT(ROW()/10.0001)*10)

Note the .0001 used instead of .1


Report •

#6
June 9, 2010 at 06:49:32
Thanks! That worked.

Report •

Ask Question