This is post
https://excelfox.com/forum/showthread.php/2837-Appendix-Thread-App-Index-Rws()-Clms()-Majic-code-line-Codings-for-other-Threads-Tables-etc-)?p=19586&viewfull=1#post19586
https://excelfox.com/forum/showthrea...ll=1#post19586
https://excelfox.com/forum/showthrea...ge54#post19586
https://excelfox.com/forum/showthread.php/2837-Appendix-Thread-App-Index-Rws()-Clms()-Majic-code-line-Codings-for-other-Threads-Tables-etc-)/page54#post19586




In support of this main Forum Post http://www.eileenslounge.com/viewtop...301714#p301714

Do the transpose a bit differently using Application.Index


Consider this example of a Selectioned range:
a b
c d
e f

,and we want an output of some form like a b c d e f
Consider first a single column such as the first,
Let vTemp = Application.Transpose(Selection.Columns(1)) ' gives - {"a", "c", "e"}
We can Do the transpose a bit differently using Application.Index
Let vTemp = Application.Index(Selection, Array(1, 2, 3), Array(1, 1, 1)) ' - {"a", "c", "e"}
What's going on: Excel / Excel VBA is doing what it often does, along a row, then down a column, sometimes referred to as array type calculations, in this case the argument arrays are followed leading to an output of a form of the 1 dimensions, ("pseudo horizontal") array , as we want. The index works three times on each pair of co ordinates, each time giving the result in the way Index would in the more conventional way for just 1 pair of row and column co ordinates
Using this index way we are not restricted to a single column, we can pick any co ordinates we chose.
The next co ordinates give us a simple single line of all our cell values
Let vTemp = Application.Index(Selection, Array(1, 1, 2, 2, 3, 3), Array(1, 2, 1, 2, 1, 2)) ' {"a", "b", "c", "d", "e", "f"}
' Or
Dim Rws() As Variant, Clms() As Variant
Let Rws() = Array(1, 1, 2, 2, 3, 3): Clms() = Array(1, 2, 1, 2, 1, 2)
Let vTemp = Application.Index(Selection, Rws(), Clms()) ' ' {"a", "b", "c", "d", "e", "f"}

To make a more useful flexible solution, what we need to do is to get those array arguments dynamically from, in this example, the Selection
So on the face of it, it is quite easy what we need to do: It is usually just some seemingly clever, but actually quite basic maths. It is helpful perhaps to get in the head where/ why the problem / need for the maths comes in. It’s the difference between computers and us: Computers keep going, often things are listed or numbered in sequential number s ( example see item number way of doing things , https://excelfox.com/forum/showthrea...column-looping ) but we use a paper/book or screen so keep going back/ (returning to the left) then up/down, (“Line feeding”)
In the various maths, the row count would usually feature less, or will feature in a similar way for slightly different examples, since mainly it effects ( along with the column count ) a final total number. In some examples the total number may be used and so the row count may never feature in any maths.
The column count is more prominent, since this size restriction is the human wanted chopping up of things to get them within our limited view, ( Generally we have a more limited width ( column ) than length (row) perception: we scroll more hapilly down than across. I don’t. My brain is more open minded and wide.
So we should be thinking more in terms of column count effecting things

Consider the requirement for the Rws()
For the case of more than one column, the sequential numbers need to be repeated for as many times as we have columns. So it is possibly a good guess that some division of that column count would be useful. Doing that division will give us
{.5, 1, 1.5, 2, 2.5, 3}
Observation of that shows we see something similar in the whole part of the numbers to what we want. But we don’t quite get what we want by taking the integer. We get
{0, 1, 1, 2, 2, 3}
We need to do something to correct what we get to what we want. It may not be obvious what we should do. If we consider another example it might help. So let’s consider 3 columns. Applying the same logic we would get
{0, 0, 1, 1, 1, 2}
It seems that the numbers which are to be dived by the column count and then taken the integer of, should not be {1, 2, 3, 4, 5, 6} , but for the case of 2 columns
{2, 3, 4, 5, 6, 7}
, and for the case of 3 columns
{3, 4, 5, 6, 7, 8}
So it looks like we want to add (ColumnCount-1) before the integer is done

Consider the requirement for the Clms()
We want to keep repeating the column sequence. The Mod function is promising for this, since it gives us what is left over after taking off as many as possible of the given number in the second argument. So whatever this gives us, will be repeated .
This, Mod(Column(A:F),2) , almost gets us there with {1, 0, 1, 0, 1, 0} and checking the same logic for 3 columns we have {1, 2, 0, 1, 2, 0}
We need to tweak that to get us to start at 0 and then add 1

_.______


Those ideas are incorporated into the full code version in the next post, https://excelfox.com/forum/showthrea...ll=1#post19591

Ref:
http://www.excelforum.com/excel-prog...umn-range.html
http://www.excelforum.com/excel-prog...e-columns.html