It is (fairly) well known that when using Worksheetfunction.Transpose on an array in VBA the array must be no more than 65,536 (2^16) rows long, or it will return an error.
Recently when using Excel 2016 (32 bit) I thought this limitation had been fixed; I was able to transpose arrays with up to the maximum number of rows allowed by Excel (1,048,576 0r 16 * 2^16), without generating any error. Then I checked the end of the returned arrays, and discovered that they had in fact been truncated at 65,536 rows. A closer look revealed that the truncation is in fact done in blocks of 2^16 rows, so if you transpose an array of 65,537 rows the operation will delete all but 1 of them, with no indication that anything unusual has happened.
To investigate what is happening I wrote 7 functions to split an array into single columns, then transpose them, the intent being to generate 1D arrays, which are much faster when transferring large data sets to other programs. The variations were:
- Splitarray: Split a 3 column array into 3 single columns, with no transpose.
- Splitarray-a: Split and transpose in a single operation.
- Splitarray-b: Redim the output arrays first, then split and transpose in a single operation.
- Splitarray-c: Redim the output arrays first, then split into three variant arrays, then transpose.
- Splitarray-d: Transpose without splitting, creating a 3 row array.
- Splitarray-e: Split into single columns, then copy to 1D arrays with a loop.
- Splitarray-f: Convert the input range to a variant array, then split and transpose with a loop.
The functions were modified to return the following information:
- Row 1: Run time
- Rows 2-4: The number of values in the returned arrays, or in the case of Splitarray-d the number of rows, then the number of columns.
In the case of Splitarray-c and Splitarray-f, which had a two stage operation, two columns were returned, with times and array sizes at the end of each step.
The results for Excel 2016 are shown below for four different array sizes:
65,536 rows; all functions return all the input data
1,048,576 rows: the first function (which did not transpose) and the last two (which used loops) return all the data, but all the others truncate the output to 65,536 values.
65,537 rows: The second to fifth functions have all truncated the data to just one value.
1,048577 rows: Now all the functions return #VALUE! errors (as in previous versions of Excel), because the specified range size exceeds the maximum number of rows. Note that VBA arrays can have a much greater number of rows (limited by available memory), but if you are reading data from the spreadsheet the range limit on number of rows applies.
Comparing with Excel 2010; results for 65536 rows are the same:
With 1 additional row up to 1,048, 576 rows, all the functions that use Transpose return #VALUE! errors:
And with 1 or more rows past the range limit all the functions return errors:
It was my recollection that Excel 2013 worked the same as 2010, but checking with 65,537 rows I found that the arrays were truncated to one value, when Transpose was used, as for Excel 2016.
So the conclusions are:
- In Excel 2013 and 2016 (32 bit) using Worksheetfunction.Transpose in any VBA routine will truncate the array without warning if it has more than 65536 rows, so if you must use this function be sure to check the size of the array first.
- But really, there is no good reason for using it at all. It can be replaced by a short VBA routine using loops, which will return the full array up to 1,048,576 rows (or larger if working entirely in VBA), and is actually faster than the Transpose version (at least for arrays of the size used in these tests).
Newton Excel Bach, not (just) an Excel Blog 
Thanks for bringing this behaviuor up.
I recently decided for this reason , along with the speed aspects, to always use my own functions for transposing Arrays.
http://www.excelforum.com/tips-and-tutorials/1137811-functions-to-re-dim-preserve-first-dimension-in-2-d-array-and-transpose-byvalue.html
I have also often noticed that working with single dimension Arrays can be very quick.
You mentioned splitting your Array into columns before transposing each one. I have found that splitting an Array can be very quick.
However one is limited again to the earlier Worksheets size.
And I do not know of a quick way to join rows
So, a couple of questions:
_1 ) I expect there would be no advantage doing this in an Array Transpose Function.
I am a VBA Novice / part timer, so just thought I would check that I have not missed anything?
_2) I do not have above XL 2010. It is not clear to me immediately what I “lose”. When it “Truncate”, what values does it transpose, do they include the rows above the truncated, so you never get the rows above 65,536 , or does it truncate as it were from the bottom-
For example, if I try to transpose 65537 rows, I get 1 column,- Correct? But, do I get row number 1 or row number 65537 transposed as my final result.
Thanks
Alan
P.s. I do note that some of these functions work very efficiently with a Spreadsheet, and that sometimes not always the Array Methods are the best
LikeLike
Hi,
I am a computer novice, only having started having anything to do with computers since a couple of years. So please be gentle on me if I am talking rubbish here !!.. _..
_.. Considering the poorer speed performance that I often see when using an Array in a Worksheets Function compared with using a Range, I would suggest that possibly Excel “pseudo” places the Array Member Items into some sort of “pseudo Range Object”, does the operation on that , and then reconverts to an Array of values .
Outside my knowledge bounds , but suggested to me by advanced programmers is something along the lines of this….” … Excel’s normal “domain” is a set of addresses represented in a rectangle 256 by 65536 …..if they had to handle addresses outside those ranges it would be gruesome….”… This results in the bounds of Worksheet Functions when used for Array Value matrixes still being set to the old pre XL2007 worksheet Limits of 65536 rows. ( I am not sure if the columns maybe possible to be increased in later XL versions). The “pseudo Range Object” could be set to these bounds, or at least to the 65536 row bound.
I am suggesting that the Array Member Items will be referred to ( for read and write ) through the Range Object Item Property. In general , ( or in most cases) , the upper bound of the Range Object Item Property is above the total number of cells in the Range Object to which it is applied. My experiments have shown, that when the item Number exceeds the total number of cells, then the range “filled” through referral to the Range Item Property, continues to be filled following the rows, then columns convention. Effectively, the final built Range Object will extend “down” effectively stacking “downwards” Range Objects of the original Range size. Eventually we end up with a Range Object of the same column size as the original, but with extended rows. When, following the row , then column convention, we attempt to go down further then the maximum row limit, ( that is to say we reach “bottom right limit” ), then for all worksheet functions, I think they are “refreshed “and operations begin again from “top left”.
I think the above could explain the “truncation effects” reported here.
Possibly the earlier XL versions errored as the maximum Range Item Property Index ( Item number ) was limited to the 256 x 65536. This maximum Range Item Property Index ( Item number ) may have been increased for the later versions.
Alan
LikeLike
Hi Alan, are you the Alan Elston who sent the first comment? If so, apologies for not responding before (if not, apologies to Alan Elston).
From the first comment:
I will have a look at using 0 with the Index function to extract a single row or column. I don’t think I have used that. Using Index with Transpose might be a quick way to extract a 1D array from a range, although it would have the 65536 cell limit.
When you transpose a very long array you lose the end of the array. For instance, if you transpose a single column with integers 1 to 65538 it truncates the last 65536 rows, so you get 1, 2, #NA, #NA … and so on.
From the recent comment, I don’t know the details of how these things are handled in Excel, but in some cases Excel handles ranges up to 1 million+ with no problem, and VBA also handles very big arrays with no problem. Your suggestion for what is happening in the new versions sounds reasonable, but I think that returning a truncated array without warning is definitely a bug, that should be fixed.
LikeLike
It seems I had seen the Index trick before!:
https://newtonexcelbach.wordpress.com/2013/06/23/using-index-as-an-array-function/
LikeLike
Hi “dougaj4”
Yes I am Alan, Alan Elston, ( Doc.AElstein AT most places ).
It is a shame I have no access to Excel above XL 2010 – I might have come to my vague explanation idea a bit quicker. (The idea came after I finally realised that there is, ( in addition to the well known Range Range Property), also a less publicised Range Item Property ( and consequently what most people and Blogs explain as the Cells Property is incorrect .. http://www.excelforum.com/showthread.php?t=1154829&page=11&p=4551144#post4553399 ) )
What is a bug and what is down to just knowing what is going on seems to be argued by much more senior Excl people than me. I tend to prefer just to know as much as I am able of how the things are working. I guess strictly speaking a bug is when something does not do what it is stated to do. But once it is again personal opinion really. Because of my limited computer knowledge I am very grateful when people such as yourself give their opinion, so thanks for that.
I experimented a lot with Index and Index tricks with Arrays. I went back partially to using Ranges, at least partly, for example as the first argument, after I hit various problems . http://www.eileenslounge.com/viewtopic.php?f=27&t=22512
I then noticed that they can in some cases be much more efficient and avoid some problems, and, of course have the added advantage of avoiding the size limitations we are discussing.
Note that for Using Index to extract a 1D Array ( actually 2 D Array of 1 “column” or 1 “row” ( – I often refer to them all as “1 Breadth” Arrays ) ) from a Range, ( using the “Index slicing” trick technique), it does not involve using .Transpose provided the first argument ( Array or Range ) is set to the maximum row or column size of that you wish to extract.
If the maximum row or column size that you want returned is less than that of the maximum row or column of the first argument Array or Range, then you must use the other Index trick. The use of .Transpose is then sometimes used on the second argument as one convenient ways to produce a “vertical” Array when using this other Index trick. ( This trick allows you return an Array of values of any specific rows and columns combination from the first argument Array or Range. So you could choose , for example, one “row” and the specific “columns” required ). But you can do that “trick” without any use of any transposing. This other index trick is typically done such:
http://www.excelforum.com/showthread.php?t=1099995&highlight=
http://www.mrexcel.com/forum/excel-questions/908760-visual-basic-applications-copy-2-dimensional-array-into-1-dimensional-single-column-2.html#post4375354
( Typically you would use the “ Evaluate(“ Row .. “ ) “ type technique to get your second argument “ vertical “ Array for consecutive rows, or you would simply build a 2D 1”column” Array of your required indices. This latter could be done by building a string of indices required ( separated by say a space ) , followed by VBA.Strings.Split, and then followed finally by .Transpose. However, I think for a single “Breadth” Array, we would agree that transposing with Arrays is a very efficient alternative to using .Transpose
( I do not know why in the USEFUL GYAAN blog that he uses the transpose so much. It is not necessary and in many of his codes he transposes, then transposes back unnecessarily. I have questioned that many times, but with no answer. ). I believe many have copied the original codes, and I continually correct OPs in Forum Threads who are .Transposing then .Transposing back unecerssarily.
Note also, – I have noticed that if you are using the Index slicing trick to extract a 1D Array ( actually 2 D Array of 1 “column” or 1 “row” ) of an entire row or column from a first argument Range , then , if you declare the receiving variable as Range, then you actually get from Index a Range returned. (You can then apply the various “value” Properties, such as .Value to that to return your required Array. Of values
http://www.eileenslounge.com/viewtopic.php?f=30&t=25517#p198321
This appears once again quicker anyway than using an Array as first argument, and you are not limited to the size constraints we have been talking about.
( The other Index trick will not return a Range Object under any circumstances as far as I have been able to find out. As it returns an Array of values that output Array will have the size limits we have been talking about. Note however you can use the entire worksheet as your first argument as a Range Object ( using .Cells Property ) . This I have found to be very useful and efficient also, – but it does have just one annoying “Bug” !? of loosing date info, that is to say it defaults to using .Value2 in this case rather than the usual .Value default, that is to say , despite returning a Field ( Array ) of Variant types, it “looses” the date format.)
Thanks for the Reply, Best wishes for the New Year
_ A.E.
LikeLike
Edit: just a missed a last reference for the lost date bug thing
http://www.eileenslounge.com/viewtopic.php?f=30&t=22787#p196639
_ Alan
LikeLike