Transpose function limitation Excel 2016 and less

Hi,

I would like to ask you about transpose limitations.
It is a function which i am often use while outputting data from dictionary or array.

In Excel less than 2016 there was an error where array variable exceed 255 characters.
Can anybody confirm that in Excel 2016 the problem is solved?

In my case on Office 2016 the code below is working like a charm:

Please Login or Register  to view this content.

for 2013 i do not think so.
So bug was fixed?

And what about number of rows limit, how to avoid this error?
Link: link

and the most important question - how to replace transpose function?

Please discuss,
Best,
Jacek

hi alansidman,

thank you i like power query.

hi RoryA,

Ok but question is about limitations - can anybody confirm that formula above is working in Excel 2016?
For it is but it should throw an error because of 255 character limit.

Best,
Jacek

jarysek - definitely fixed in excel 2016 - works properly

great!

thank you.

Jacek

Hmmm,

what is:

Column width

255 characters??

https://support.office.com/en-us/art...7-269d656771c3

This is not for transpose function?

JacekScreenshot_10.png

thank you!

Jacek

OK, then can you just test this code and read the message?

Please Login or Register  to view this content.

It shows 1 on xl2013.

Hi jindon,

it is also showing 1 on xl2016.

Best,
Jacek

Thanks, it still has such limitation then...

Thanks, it still has such limitation then...

why 1 is telling this? What does it mean?

Transpose only works properly when Upper bound is equal or less than 2 ^ 16 = 65536.
Problem of this is NO ERROR...

Please Login or Register  to view this content.

So if you are applying transpose to range it will work without any problems and limitations, am i understanding this correctly?

Jacek

thank you Rory,

so what does it mean?

IIRC it only works properly when applied to a range

Jacek