To transpose or not to transpose? What decides? Table / Array?

Hi all, here is the code suggested to me (by nigelog with the aid of jindon) from a previous thread I posted. As I'm still just learning VBA I am trying to make sure I understand all aspects of it.

Please Login or Register  to view this content.

My question is about the Application.Transpose, in another thread " What to avoid in VBA" the following code line is written by shg:

Please Login or Register  to view this content.

So why does the code need the transpose, and what decides it?
Do I absolutely need an array in that code if I'm working within a table? (listobject) (It is important to refer to the columns by their headers!! They are also not adjacent.)

Also what does the 0 "decides" after the .Value, what would change if it was 1 in the code line?

I got a few answers so far saying about 1 dimensional and 2 dimensional vectors.. but I think I need this a bit more "visually".. I can see that Range ("A1:G1") is a single line (1 dimensional?), but the way I see it listrow(i) is also just a single line (1 dimensional?)? Or is it because its not every value I need concatenated from listrow(i) only 3 of them from 3 not adjacent columns?
Does x really need to be declared and the array defined, could it not be that instead of x in the "DictKey=" line we would just write the columns identified by their headers? Or is it 2 dimensional BECAUSE I'm identifying them by their headers? As it can't just count from left to right it has to look up to the top of the table to check the column header?

Can somebody help to clear this up for me?

Thank you!

Links:
My previous thread: https://www.excelforum.com/excel-pro...al-failed.html
"What to avoid in VBA" thread: https://www.excelforum.com/excel-pro...in-vba-11.html

If I write it like this:

Please Login or Register  to view this content.

I get Run-time error 1004 invalid number of arguments?

If I leave the x array as it is but remove the transpose it works.. so I guess it wasn't necessary, but still. Why? And why x array is necessary?

So basically tricking Excel to return 1 object.. which consists of 3 columns.. because it can't return 3 objects which would be the 3 columns?

Achieves the desired 1D array because of the .Listrow(i)?

https://www.excelforum.com/excel-pro...ml#post5065133

oh wow that version doesn't even have Application.Index in it anymore.. maan if its possible to get even more confused

anyway I presume its safe to conclude, Join(Array .... is more powerful than Join(Application.Index .... ?

cause in that case I will just abandon Join(Application.Index .... completely...

I've just posted such lines just because you wanted to rewrite your original code without showing what you are trying to do.

Please Login or Register  to view this content.

Originally Posted by nigelog

You will find that

Please Login or Register  to view this content.

also needs to be transposed.

What needs to be transposed? I tried the original code suggested by you (with the help of jindon) and it worked without the transposes in it...

One more question:

What does the 1 stand for in this line? What would change if it was 0?

Please Login or Register  to view this content.

Range(RowIndex, Colum Index) based on ListRows.

Just try change the value for yourself.

I thought row index in this case was i?

No, it should be 1 ONE.

I said Row/Column Index BASED on ListRows(), means Range(Row/Column) from ListRows(i).
1 refers to the row that is the same row, 2 refers to one row down from the ListRows(i).

Oh okay thats something new I learned from you again!
Thank you!