vba problem with 'variant=application.worksheetfunction.transpose(range)'

Question

tested in 2007, 2010

I use   variant=application.worksheetfunction.transpose(range) to read in a sheet to which I will add rows.

This works fine for 1- 2^16 rows,

but for sheets with greater than 2^16 rows , this returns the first 'maxrows mod 2^16' rows.

Thus if your range is A1:B65537, you get exactly 1 (one) row.

I have not found any documentation that indicates that I have crossed a limit or that this is desireable behavior.

If you try to transpose an array with greater than 2^16 rows TO a sheet, there is an error- 13,  "type mismatch".

If this the expected behavior and where is it documented ?

If this is a bug, is it one that will be fixed ?

thanks

David

Answer 1

We can debate the wisdom of the TRANSPOSE function's limitations, but that doesn't solve your problem.

Here's the major issue to overcome....You can work with Excel or against it.

From your comments, it appears that some effort has already been expended with the dependency that the TRANSPOSE function can accommodate the ranges you're referencing. Evidenlty, that is not the case. Every developer runs into that kind of problem at some point. Experience teaches us to quckly recognize those situations and aggressively pursue other avenues.

If you can give us a description of what you want to accomplish, perhaps we can help.

Answer 2

Sometesting that I did last month demonstrated that in Excel 2007 / VBA the limit of  WorksheetFunction.Transpose was, indeed, 2^16 elements.  I was not able to find any documentation.

Answer 3

If you try to transpose an array with greater than 2^16 rows TO a sheet, there is an error- 13, "type mismatch".

Hello David,

If I am understanding your problem correctly then 2^16 rows can't be transposed to 2^14 columns which is max columns available.

Also there is reference to the maximum for tranpose to an array at the following link so it appears that you have probably found the limit.

http://www.mrexcel.com/forum/showthread.php?t=540078

Answer 4

Hi

I will try to do better.

What Am I Doing

I have a sheet with about 70,000 rows which I need to manipulate and compare with other data using existing VBA routines.

The sheet is read into a 2-dimensional variant array where it is manipulated, lines sometimes added, and written back to the sheet.

The statements for reading the sheet into the array is:

vartable = application.worksheetfunction.transpose(range("A1:AB<last used row>")

This operates nearly instantly and is very much faster than reading the data in one cell at a time.

The data needs to be "transposed" because the number of rows may change and you can only Redim Preserve the last dimension of an array  (thus it needs to be   varTable(col, row)).

As soon as the sheet grew to more than 65536 rows, this stopped to work and instead of loading all the rows into the variant array, it only loaded         <last used row> mod 2^16 rows.

Examples

rows on sheet          rows loaded

65535                          65535

65536                          65536

65537                                  1

65538                                  2

...

I have found no documentation as a feature/aspect of the  .transpose function. 

I suspect it to be a bug left from the transistion of 2003 to 2007;  and that somewhere inside Excel or VBA,  a 16-bit integer is being used where a 32-bit  long should be used.

My question is:   is this an aspect?   if a bug, is it ever likely to be fixed?

are we doomed to loading arrays larger than 65536 lines one cell at a time?

The workaround (e.g. if this technology didn't exist, what would you do) is obvious; the question is why this technology breaks down and is there a documented limit that says this is what to expect.

?

enuf?

david

Answer 5

I think if you explain what you want to do, we may be able to

1. explain what's happening in your approach

or

2. suggest a better way