Come convertire una colonna di dati in più righe

Sub trasponi()
r = 10 'riga inizio trasposizione
c = 5 'colonna inizio trasposizione
r1 = 10 'riga inizio dati col. B DEVE CONTENERE ORA
ur = Range("B" & Rows.Count).End(xlUp).Row 'ultima riga col. B
CelStart = r1
CelEnd = 0
For j = r1 + 1 To ur
    If InStr(Cells(j, 2), ":") > 0 Then
        CelEnd = j - 1
        Range(Cells(CelStart, 2), Cells(CelEnd, 2)).Copy
        Cells(r, c).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
        r = r + 1
        CelStart = CelEnd + 1
    End If
Next
Stop
Range(Cells(CelStart, 2), Cells(ur, 2)).Copy
Cells(r, c).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=True
End Sub

Sub Disponi()
Dim r, c, x, rng

Range("E10:U1000").ClearContents
r = Cells(Rows.Count, 2).End(xlUp).Row
rng = Range("B10:B" & r)
r = 9
For x = 1 To UBound(rng)
    If InStr(rng(x, 1), ":") > 0 Then r = r + 1: c = 5: Cells(r, c) = rng(x, 1)
    If InStr(rng(x, 1), ":") = 0 Then
        c = c + 1
        If IsNumeric(rng(x, 1)) Then Cells(r, c) = Val(rng(x, 1)) Else Cells(r, c) = rng(x, 1)
    End If
Next
End Sub