VBA to automatically transpose X rows of data into columns

[javajoe]

Hey folks -

Could really use your help with this one. I have baseline VBA knowledge but not enough to do the following:

I have an Excel worksheet with 200 "logical records" of data, currently represented by ~ 12,000 rows of data in just one column (A). Each logical record is actually 60 rows of the data. So what I need to do is come up with a macro that will do the following and save me from 5 hours worth of mind-numbingly-boring transposing and deleting of rows:

1) Select 60 rows of data.
2) Copy/Paste that selection, then TRANSPOSE onto row one, starting at A1 and going through BH:1 or whatever 60 cells comes out to be horizontally
3) Then Delete the rows of data that were transposed into row 1 columns (basically delete 60 rows)
4) Then select the NEXT 60 rows of data, and repeat the whole process, but this time pasting into row 2 instead of row 1 (A2:BH2).
5) This needs to iterate through the whole worksheet until it cycles through all 12000 rows.
6) The end result will be 200 rows of data, with values in the first 60 columns

Make sense? Any help from those more versed in loops and iterating through a worksheet would be GREATLY appreciated!!!

 

[Norie]

Try this.

Sub Trans60()
Dim rng As Range
Dim I As Long
    
    Set rng = Range("A1")
    While rng.Value <> ""
        I = I + 1
        rng.Resize(60).Copy
        Range("B" & I).PasteSpecial Transpose:=True
        Set rng = rng.Offset(60)
        
    Wend
    rng.EntireColumn.Delete
End Sub

 

[javajoe]

Wow. That was perfect. Now... what to do with all my free time!
Thanks again Norie.

 

[PatrickWW]

Brought this one back from the dead, but it was very helpful. Thanks Norie!