Microsoft Office excel 2007 home & stude...

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

879841268And 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.

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?

Sorry for the confusion, yes, they are all in different cells. So

for example:A1:E1 -- 38274

A2:E2 -- 67895

A3:E3 -- 15937And 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

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

needed.

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.

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.

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

Thanks! That worked.

Ask Your Question

Weekly Poll

Do you think manufacturers should do more to reduce phone and tablet usage among kids?

Discuss in The Lounge

Poll History