vba - Transpose Excel column to rows

Question

I have an Excel sheet that looks like the first picture and I want to convert it to the second picture:

I have written the following code but it does not work as expected. It deletes more rows than expected. What's wrong with the code?

Sub Trans3()
Dim rng As Range, rng2 As Range
Dim I As Long
Dim J As Integer, Z As Integer, Q As Integer, T As Integer

Set rng = Range("B1")
While rng.Value <> ""

 For Each y In Range("A1:A10")
    I = I + 1
    J = I
    Z = 1
    Do While Cells(J + 1, 1).Value = Cells(J, 1).Value
        J = J + 1
    Loop                     
    Set rng2 = Range("B" & I & ":B" & J)

    If I > 1 Then
       Z = J - I + 1
    Else
        Z = J
    End If

    rng2.Resize(Z).Copy
    Range("C" & I).PasteSpecial Transpose:=True
    T = I

    Do While J > 1
       Q = T + 1
       Rows(Q).EntireRow.Delete
       J = J - 1
   Loop

 Next y
Wend

End Sub

Answer 1

So I did a little refactoring. I moved everything into arrays to speed it up.

See notes in code for reference.

Sub FOOO()
Dim inArr() As Variant
Dim outArr() As Variant
Dim ws As Worksheet
Dim cntrw As Long
Dim cntclm As Long
Dim i As Long
Dim j As Long
Dim k As Long
Dim rng As Range

Set ws = ActiveSheet

With ws
    Set rng = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
    'find the max number column that will be needed in the output
    cntclm = ws.Evaluate("MAX(COUNTIF(" & rng.Address & "," & rng.Address & "))") + 1
    'find the number of rows that will be needed in the output.
    cntrw = ws.Evaluate("SUM(1/COUNTIF(" & rng.Address & "," & rng.Address & "))")
    'put the existing data into an an array
    inArr = rng.Resize(, 2).Value
    'resize output array to the extents needed
    ReDim outArr(1 To cntrw, 1 To cntclm)
    'put the first value in the first spot in the output
    outArr(1, 1) = inArr(1, 1)
    outArr(1, 2) = inArr(1, 2)
    'these are counters to keep track of which slot the data should go.
    j = 3
    k = 1
    'loop through the existing data rows
    For i = 2 To UBound(inArr, 1)
        'test whether the data in A has changed or not.
        If inArr(i, 1) = inArr(i - 1, 1) Then
            'if not put the value in B in the next slot and iterate to the next column
            outArr(k, j) = inArr(i, 2)
            j = j + 1
        Else
            'if change start a new line in the outarr and fill the first two slots
            k = k + 1
            j = 3
            outArr(k, 1) = inArr(i, 1)
            outArr(k, 2) = inArr(i, 2)
        End If
    Next i
    'remove old data
    .Range("A:B").Clear
    'place new data in its place.
    .Range("A1").Resize(UBound(outArr, 1), UBound(outArr, 2)).Value = outArr
End With
End Sub

This does require that the data be sorted on column A.