how to loop macros for the all rows ?

[veer-india]

i have all the data under A
i want move
a1 to a1
a2 to b1
a3 to c1
a4 to d1
a5 to e1
a6 to f1

and then again
a7 to a2
a8 to b2
and so on....
and the file is too large to do manually
please write a macro for me....
i dont know much about excel.... please give me the code.

 

[dermie_72]

quick check showed 00:13 secs. I tried your code, on the same data and it came back 00:12 secs. Pegged at the post.

 

[Michael M]

OK...your code....11.106 seconds
my code.... 10.157 seconds
for 10000 rows

Now what's the bet.....somebody like Rick will come back with, like, 3 seconds.....ray:

 

[dermie_72]

i must have an antiquated machine. LOL

 

[Rick Rothstein]

@Michael M and/or dermie_72,

Would one or both of you test how fast this code runs against your data (just guessing here, but I believe it will execute noticeably faster than either of your macros)...

Sub MoveColumnAtoRow1()
  Dim R As Long, C As Long, RngIn As Variant, RngOut As Variant
  Const ColsAcross As Long = 6
  RngIn = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
  ReDim RngOut(1 To 1 + (UBound(RngIn) \ ColsAcross), 1 To ColsAcross)
  For R = 1 To UBound(RngIn)
    RngOut(1 + (R - 1) \ ColsAcross, 1 + ((R - 1) Mod ColsAcross)) = RngIn(R, 1)
  Next
  Application.ScreenUpdating = False
  Columns("A").Clear
  Range("A1").Resize(1 + (UBound(RngIn) \ ColsAcross), ColsAcross) = RngOut
  Application.ScreenUpdating = True
End Sub

 

[dermie_72]

an impressive 0.17 seconds here (mind you it's an old machine so it's probably much faster than that)

 

[Michael M]

Yeah, not that flash......0.9 of a sec.....I knew you could do it Rick...ray:

 

[Michael M]

Having said that, to be fair, once we included screenupdating, both me and Dermie were below 2 seconds !!!

 

[Teeroy]

OK...your code....11.106 seconds
my code.... 10.157 seconds
for 10000 rows

Now what's the bet.....somebody like Rick will come back with, like, 3 seconds.....ray:

You picked it Michael. Now who do you like for the election and what odds can we get?


Since you have a Timer set up what's your speed run on:

Sub transpose()
varray = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
Range("A1").Resize(1, UBound(varray)).Value = Application.transpose(varray)
End Sub

 

[Michael M]

Teeroy
We ALL have a timer !!
Your code took 0.189 secs
But it transposes everything to row 1

Sub transpose()
t1 = Timer
varray = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
Range("A1").Resize(1, UBound(varray)).Value = Application.transpose(varray)
t2 = Timer
MsgBox t2 - t1
End Sub

 

[Michael M]

Oh, forgot....I won't be surprised if Kev 07, gets up.....but what about the ashes....