Transpose Rows in Columns in MS XLS using VBA

Question

I have an XLS for which I need to A1:H1 to say A1:A7. I can achieve this by using the transpose function in copy. However I need to do this as part of VBA script. I don't seem to find any equivalent function.

Answer 1

Same, basically.

You can use Copy:

source_range.Copy
dest_range.PasteSpecial Transpose:=true
application.cutcopymode = false

Or you can avoid using it (if you only need raw values):

dest_range.value = application.worksheetfunction.transpose(source_range.value)