Explain App.Index(Rng, Rws(), Clms()) (multicolumn Combobox with Index.application)

**DocAElstein** · 02-28-2022, 06:49 PM

Hello Amelynn
Welcome to ExcelFox , the thinking man’s Excel forum

Originally Posted by Amelynn

Who can help me, ....

I think, therefore I am , and able to help you.

Originally Posted by Amelynn

...... I don't understand how the line ".List
= Application.Index(rng, Evaluate("ROW(1:" & rng.Rows.Count & ")"), Array(1, 3))
" works and I can't stay So....

To Explain

Question: _(i) What is Array(1, 3) ?
Answer_ It is like –
___1 _3
( Sometime we write in excel spreadsheet convention {1, 3} , but this usually means the same thing )

Question: _(ii)a) Evaluate("ROW(1:" & rng.Rows.Count & ")")
Answer:

Originally Posted by Amelynn

..... evaluate (…….. used to occupy excel formulas in vba) with row,

Correct! Good! - Because you understand this, it makes the explanation for me much easier.
We are really only interested in understanding what is , =ROW(1:" & rng.Rows.Count & ")" , in excel spreadsheet formula

Question: _(ii)b) What is =ROW(1:" & rng.Rows.Count & ") ? ( in excel spreadsheet )
For your range, rng = B4:D8 , rng.Rows.Count = 5
=ROW(1: " & rng.Rows.Count & " )
=ROW(1: " & 5 & " )
=ROW(1:5)

( Green is Excel Spreadsheet, Blue is VBA in string “ “ in Evaluate( “ “ ) )

The excel spreadsheet Row( ) function is usually like for
Row( A1) = 1
but can also return a “vertical” array of values like
Row( A1:A2) = 1
Row( A1:A2) = 2
Inside VBA Evaluate “ _ “ we find that we are decoupled from spreadsheet absolute values, and so in inside VBA Evaluate “ _ “
Row(A1:A2) = Row( 1:2) = 1
Row(A1:A2) = Row( 1:2) = 2

ROW(1:5)
It is like
1
2
3
4
5
( Sometimes we may write
{1
2
3
4
5}
or sometimes we may write in excel spreadsheet convention , ={1;2;3;4;5} , but usually this means the same thing)
Note: For “vertical” array some excel use ; but some Excel use \ – So sometime you may need ={1\2\3\4\5}

Sometimes if we are lucky, Excel will try to do array calculations and return you an array.
Like

Code:

	{	1	2		X	{	6	8		  =	{	1, 6	2, 8	
		3	4	}			7	9	}			3, 7	4, 9	}

But if you ask it to do = Index ___ , ROW(1:5)_ ,_ Array{1 , 3} it tries to look at columns and rows not specified.
This should not work

Code:

=  {	1	?			   {	1	3	
	2	?				?	?	
	3	?		X		?	?	
	4	?				?	?	
	5	?	}			?	?	}

???? So we have a problem ?,
But we can be lucky again, because then Excel will guess to see the following instead, ( actually its more complicated then that, more precisely it is due to Excel VBA Interception and Implicit Intersection , but we often say that Excel guesses things, as its often written to get things correct when you miss things out. ( In this particular case it is not clear if the phenomenum occurs by accident or design ) )

Code:

=  {	1	1			   {	1	3	
	2	2				1	3	
	3	3		X		1	3	
	4	4				1	3	
	5	5	}			1	3	}

( What is actually happening there above in those last two sketches is: -
If Excel is given a single row or a single column, but is being required to look at values of further adjoining rows and columns where no values are given, then the effect of the phenomena of Excel VBA Interception and Implicit Intersection is that in certain situation the missing values will effectively be taken as a duplication of the values in the given row or column )

So, Index will try to give us

Code:

_______    Index(    (    ,	1, 1	1, 3	
				2, 1	2, 3	
				3, 1	3, 3	
				4, 1	4, 3	
				5, 1	5, 3	  )

So if your range is, rng = B4:D8
-__=

Code:

	_B4	_C4	_D4
	_B5	_C5	_D5
	_B6	_C6	_D6
	_B7	_C7	_D7
	_B8	_C8	_D8

then Application.Index(rng, Evaluate("=ROW(1:" & rng.Rows.Count & ")"), Array(1, 3))
-__ =Application.Index(rng, Evaluate("=ROW(1:5)"), Array(1, 3))
-__ =Application.Index(rng, Evaluate("={1;2;3;4;5}"), Array(1, 3))
-__ =Application.Index(rng, Evaluate("={1;2;3;4;5}"), Evaluate("={1, 3}"))
-__ =

Code:

    rng  ,            1,1  1,3
                      2,1  2,3
                      3,1  3,3
                      4,1  4,3
                      5,1  5,3

-__=

Code:

    _B4 _C4 _D4       1,1  1,3
    _B5 _C5 _D5       2,1  2,3
    _B6 _C6 _D6       3,1  3,3
    _B7 _C7 _D7       4,1  4,3
    _B8 _C8 _D8       5,1  5,3

Code:

	  =     _B4	_D4
		_B5	_D5
		_B6	_D6
		_B7	_D7
		_B8	_D8

Originally Posted by Amelynn

........use an array to determine the rows as well......

Sure, this is no problem:
One way, for example, for just 1st 3rd and 5th row
Change
Evaluate("={1;2;3;4;5}")
to
Evaluate("={1;3;5}")

-__ =Application.Index(rng, Evaluate("={1;3;5}"), Array(1, 3))
-__ =Application.Index(rng, Evaluate("={1;3;5}"), Evaluate("={1, 3}"))

Code:

    _B4 _C4 _D4       1,1  1,3
    _B5 _C5 _D5       3,1  3,3
    _B6 _C6 _D6       5,1  5,3
    _B7 _C7 _D7
    _B8 _C8 _D8

Code:

	  =     _B4	_D4
		_B6	_D6
		_B8	_D8

Here a demo macro for you
Put some arbitrary values in your range "B4:D8" , then run this macro:

Code:

Sub Test()
Dim Rng As Range
 Set Rng = Worksheets("Sheet1").Range("B4:D8")
Dim RwsCnt As Long
 Let RwsCnt = Rng.Rows.Count '  is = 5

Dim arr_List() As Variant
 Let arr_List() = Application.Index(Rng, Evaluate("=ROW(1:" & Rng.Rows.Count & ")"), Array(1, 3))
 Let arr_List() = Application.Index(Rng, Evaluate("=ROW(1:" & RwsCnt & ")"), Array(1, 3))
 Let arr_List() = Application.Index(Rng, Evaluate("=ROW(1:5)"), Array(1, 3))
 Let arr_List() = Application.Index(Rng, Evaluate("={1;2;3;4;5}"), Array(1, 3))
 Let arr_List() = Application.Index(Rng, Evaluate("={1;2;3;4;5}"), Evaluate("={1,3}"))

 Let Worksheets("Sheet1").Range("A40").Resize(UBound(arr_List(), 1), UBound(arr_List(), 2)).Value = arr_List()

' To only select 1st 3rd and 5th row
 Let arr_List() = Application.Index(Rng, Evaluate("={1;3;5}"), Evaluate("={1,3}"))
 Let Worksheets("Sheet1").Range("A47").Resize(UBound(arr_List(), 1), UBound(arr_List(), 2)).Value = arr_List()

End Sub