need vba code to copy set of data in row to column

Hi

Sub test()
    Dim ar As Range
    Dim a As Variant
    Dim i As Long
    i = 1
    For Each ar In Sheets("rawdata").Columns("A:A").SpecialCells(2, 23).Offset(1).Areas
        a = Application.Transpose(ar)
        ReDim Preserve a(1 To UBound(a) + 1)
        a(7) = IIf(a(7) = "", 0, a(7))
        a(8) = Split(a(2), "$")(1)
        Sheets("EXPECTED Result").Cells(2 + i, 1).Resize(, 8) = Application.Index(a, 0, Array(2, 1, 3, 4, 5, 6, 7, 8))
        i = i + 1
    Next
    Sheets("EXPECTED Result").Columns("E").NumberFormat = "h:mm"
End Sub