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)


© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited

PrevNext