Transposing Data

[jonas10]

Hi, I am hoping someone can help me out transposing some data. Ive got a raw excel file with all the data in column A. Every 21 lines this is suppose to be posted across the same row. I've tried manually selecting every 21 lines and repasting this with the transpose option. Problem is that there are hundreds of sample sizes that i need to do this with on a monthly basis.

Is there a quick way to have every 21 lines automatically in the same row?

Thank you!

 

[Fluff]

How about

Sub TransPaste()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, c As Long, i As Long, j As Long
   Ary = Range("A1", Range("A" & Rows.Count).End(xlUp))
   ReDim Nary(1 To UBound(Ary) / 21, 1 To 21)
   For i = 1 To UBound(Ary) Step 21
      r = r + 1
      For j = i To i + 20
         c = c + 1
         Nary(r, c) = Ary(j, 1)
      Next j
      c = 0
   Next i
   Range("A:A").ClearContents
   Range("A1").Resize(UBound(Nary), UBound(Nary, 2)).Value = Nary
End Sub

 

[Rick Rothstein]

Here is another macro that you can consider...

[table="width: 500"]
[tr]
	[td]Sub TransPaste()
   Dim R As Long
   Application.ScreenUpdating = False
   For R = 1 To Cells(Rows.Count, "A").End(xlUp).Row Step 21
     Cells((R + 20) / 21, "A").Resize(, 21) = Application.Transpose(Cells(R, "A").Resize(21))
   Next
   Range(Cells(Rows.Count, "B").End(xlUp).Offset(1, -1), Cells(Rows.Count, "A").End(xlUp)).ClearContents
   Application.ScreenUpdating = True
End Sub[/td]
[/tr]
[/table]

 

[jonas10]

Thank you both!