Upper Limit for Transpose Function (?)

[JHSam]

I am using the Transpose function within VBA to send an array to an Excel worksheet. The total number of records in this particular array is about 8100. When I hit this step in the code, I get a "Type Mismatch" error. Have I gone beyond the limit for the transpose function to work properly?

Thanks.

 

[IML]

excel only has 256 columns.

 

[JHSam]

The function transposes the array to rows, so I'm within that limit. I think the problem is that the Transpose function may have an upper limit of 65536 records.

I can split the array up to handle this output.

 

[just_jon]

Yes, 65536 is XL's max rows; try to transpose a lesser amount to one column, then the rest to another column/sheet.

 

[NateO]

Hello,

I am using the Transpose function within VBA to send an array to an Excel worksheet. The total number of records in this particular array is about 8100. When I hit this step in the code, I get a "Type Mismatch" error. Have I gone beyond the limit for the transpose function to work properly?

Yes you have. In Excel '97 & '00 you're limited to 5461 elements. in XP, this limitation is removed.

You'll want to loop under your circumstances.

 

[JHSam]

Thanks!