### Transposing

When you transpose an array you are swapping the corresponding rows and columns
If you have a (2 x 6) array then its transpose will be a (6 x 2) array
There are a number of ways you can transpose an array in Excel.

#### Manual Transpose

This function will transpose a 0-based variant array and manually transpose each element,

`Public Function TransposeArray(myarray As Variant) As Variant Dim X As Long Dim Y As Long Dim Xupper As Long Dim Yupper As Long Dim tempArray As Variant     Xupper = UBound(myarray, 2)     Yupper = UBound(myarray, 1)     ReDim tempArray(Xupper, Yupper)     For X = 0 To Xupper         For Y = 0 To Yupper             tempArray(X, Y) = myarray(Y, X)         Next Y     Next X     TransposeArray = tempArray End Function `

#### Excel - Application.WorksheetFunction.Transpose

This function can also be called using the shorthand Application.Transpose.
This does not appear in intellisense because it is only available for backwards compatibility.
When you use this worksheet function on a worksheet you must enter the function as an array formula in a range.

#### Excel - Retrieve, Modify and Paste

Lets imagine that we want to take some data from a cell range, modify the contents and then paste the new data back to the worksheet
If you are retrieving data from a worksheet you will always get a 2-dimensional array, even if you only choose a single row or column.
If you step through this code you will see that the array "myarray" is a 2-dimensional array.
It is also very important to emphasise that this array starts at 1 and not 0.
The last line transposes the array and displays it horizontally.

`Public Sub RetrieveModifyAndPaste() Dim myarray As Variant     myarray = Range("A1:A5").Value     myarray(2, 1) = "some text"     Range("A1:A5").Value = myarray     Range("A7:E7").Value = Application.WorksheetFunction.Transpose(myarray) End Sub `

#### Excel - Create and Paste

If you are using lists of data in VBA then you might be working with a 1-dimensional array
If you want to put a 1-dimensional array onto a worksheet then you must transpose the array

`Public Sub PasteToWorksheet() Dim myarray As Variant     myarray = Array(1, 2, 3, 4, 5)     Range("C1:C5").Value = Application.WorksheetFunction.Transpose(myarray) End Sub `

#### Excel - Paste Special

One of the optional arguments of the Range.PasteSpecial method is a Transpose value.
The default value is False but setting this value to True lets you easily transpose the contents before pasting them.

`Public Sub CopyAndPasteSpecial()   Dim sourceRange As Excel.Range   Dim targetRange As Excel.Range   Set sourceRange = ActiveSheet.Range(Cells(1, 1), Cells(5, 1))   Set targetRange = ActiveSheet.Cells(7, 1)   sourceRange.Copy   targetRange.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True End Sub `

#### Important

When transferring a one-dimensional array to a range, the range must be horizontal (i.e. one row with multiple columns). If you have to use a vertical range instead you must first transpose the array. You can use Excels built in TRANSPOSE() worksheet function to do this.
If you try and transpose an array that contains more than 65535 (2^16) elements then you receive a type mismatch
Array Formula that try to return more than 65535 rows from a UDF fail with #Value even in Excel 2010. - KB166342
In Excel 2000 there was a restriction of approximately 6550. If this was exceeded you got an Out of Memory exception (KB177991)