Transpose Data (Column to Rows)

Hi,

Hope that an expert can help me out with this.

I'm trying to transpose the data shown in the attached example; from a column (Ex:A2:A46) into rows (Ex:C2:Y3).

Problem is that the number of rows being transposed is not a fixed number, only differentiating factor is that the it begins at a 6 digit number and ends in the cell before the next 6 digit number
(Ex:A2:A23 is the data that'll be transposed into the first row & A24:A46 the second row).

There a lot of data in the spreadsheet so if someone can help me with a VBA Macro that can automatically transpose the data, that'll help me a lot!!

Thank you,