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