# Solved: excel macro to move columns to rows



## mdsjsb (Mar 8, 2008)

I have a flat file that when I read it into excel it populates column 1 of each row with data.
This data is actually a series of 5 fields that I need to have in columns and rows, that is,
column a1 thru a5 I need placed in a1, b1,c1,d1 and e1 followed by
column a6 thru a10 placed in a2,b2,c2,d2 and e2.
The data is balanced, that ism there is data in each of the 5 fields for a "record".
Any help would be appreciated.
THanks


----------



## bomb #21 (Jul 1, 2005)

This _should_ reconfigure your data to columns 2:6 ; if it's what you need, just add a line at the end to delete the first (original) column.

Sub test()
x = WorksheetFunction.CountA(Range("A:A")) / 5
For i = 1 To x
Cells(i, 2).Resize(, 5) = Application.Transpose(Cells((i - 1) * 5 + 1, 1).Resize(5))
Next i
End Sub


----------



## mdsjsb (Mar 8, 2008)

bomb #21 said:


> This _should_ reconfigure your data to columns 2:6 ; if it's what you need, just add a line at the end to delete the first (original) column.
> 
> Sub test()
> x = WorksheetFunction.CountA(Range("A:A")) / 5
> ...


I created a new macro called test, copied your code into it, tried to run it and got a Compile Error; syntax error


----------



## bomb #21 (Jul 1, 2005)

Upload a small sample file -- say 15-20 rows of data -- so that we can be sure of your structure.


----------



## bomb #21 (Jul 1, 2005)

Another way, without macros.

Assuming you have values (say, 1 to 20) in A1:A20, this formula in B1:

*=INDIRECT("A"&COLUMN()-1+((ROW()-1)*5))*

Then copy B1 to B1:F1, and B1:F1 to B1:F4.


----------



## mdsjsb (Mar 8, 2008)

Just to make sure I didn't copy the macro wrong, I started over and it worked correctly this time. Thank you very much for your help.
mdsjsb


----------



## bomb #21 (Jul 1, 2005)

Thanks for marking "Solved" & welcome to the board. :up:


----------

