Not sure if this will help  but.... There is a transpose  option in Paste
Special. What's the result of recording a macro  that does that.

That is... Start recording a macro, Select the range to transpose, hit Copy,
hit Paste *special*, select transpose and then stop recording.

Does the code microsoft has created help you at all?

As far as rows  in the column  to transpose goes... you could use End... Down
arrow in the recording so you go to the end of the populated column.