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.
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)
TransposeArray = tempArray
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.
The number of elements cannot exceed 5461.
The array cannot contain any Null values.
The array cannot contain any items longer than 255 characters.
These can generate one the following errors:
13, Type Mismatch
5, Invalid procedure call or argument
1004, application defined or object defined error.
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)
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)
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)
targetRange.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
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)
© 2020 Better Solutions Limited. All Rights Reserved. © 2020 Better Solutions Limited TopPrevNext