Transpose Address

Hi, I am trying to recrod a macro to transpose the addresses. I have attached a spreadsheet with the first 3 entries done manually. And the second 2 entries as they are in the exisitng spreadhseet. After this i will then need to delete the empty 5 rows created. I have done this before, but now when i do it it justs keeps going back to the same cell recorded not moving across. any help appreciated.

Any there over 1000 entries, this is just a sample of the data !