Please Make sure you read up on arrays and ranges so you can better understand the code below.
Sub Transpose() ' Very quick example ' On sheet1 add into cells B2:B12, any value you like
Dim intCnt As Integer ' counter Dim intR, intC As Integer ' Row and column counters Dim arrC(12) As Variant ' tempoary storage array
intCnt = 1 intR = 2 ' Row 2 intC = 2 ' Column B
'Load Data into array Do Until intCnt = 12 'Nb re the 12 above, this is quick and dirty but there are 'many ways you can come up with this figure, or you could 'use a named range, or pass a range to a array and use Ubound(range)
'load data into array arrC(intCnt) = Sheet1.Cells(intR, intC) 'i know the size of my array here, but if i didnt 'i would use redim preserve every time i added data, to increae 'the size of the array intCnt = intCnt + 1 'increase count intR = intR + 1 'increase row Loop
'Now data is loaded into array, we will move 'through it and populate from cell D2