transpose macro question

[dc300m01]

Hello,

First off, I love this site!

Right now I have data listed in column A. The data is in 'groups', separated by a blank row. I need to transpose the data in each 'group' onto their own row. The problem is, each group will have a different number of rows.

A1 through A7 is one 'group'. Then there is a blank row. Then A9 through A12 is another 'group'. Then another blank row and so on. There are thousands of 'groups'.

What i need is A1 through A7 to be transposed to A1, B1, C1, D1, E1, F1, G1.
A9 through A12 would be transposed A2, B2, C2, D2 and so on.

I can't find nor create a macro that can account for a different number of cells in each group, and that can start a new row based on a blank cell. Any help is appreciated.

Thank you!
Jason

 

[dc300m01]

Forgot to mention I'm using 2010.

 

[jasonb75]

Welcome to the board!

I'm sure there is somebody who knows a way to do it faster / better, but this appears to work.

Please test on a copy, not your original data, this cannot be reversed if it is wrong.

Sub psuedo_transpose()
With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
Dim a As Long, b As Long, c As Long, d As Long
    a = Cells(Rows.Count, "A").End(xlUp).Row: b = 2: d = 1
For c = 2 To a
    If Cells(c, 1) <> "" Then
        Cells(d, b) = Cells(c, 1)
        b = b + 1
    Else
        b = 1: d = d + 1
    End If
Next
    Range(Cells(d + 1, 1), Cells(a, 1)).ClearContents
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
End With
End Sub

 

[dc300m01]

jasonb (my initials too! crazy) Thank you. That worked exactly how I needed it to. Many thanks to you.