Tests and Notes on Range Referrencing

[DocAElstein]

This is post #43 on Page 5 https://www.excelfox.com/forum/showt...ing/page5#post 15722
https://http://www.excelfox.com/forum/showth...ing/page5#post 15722

---

In support of this Thread: http://www.excelfox.com/forum/showth...6398#post16398




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

---

Who can help me, ....

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

...... 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

Array(1, 3)
It is like –
1 3
( Sometime we write in excel spreadsheet convention {1, 3} , but this usually means the same thing )

Evaluate("ROW(1:" & rng.Rows.Count & ")")

..... 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

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
In VBA Evaluate we find that we are decoupled from spreadsheet absolute values, and so in 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

	{	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.

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

???? So we have a problem ?,
But we can be luckyagain, because then Excel will guess to see this instead
( This is because of Excel VBA Interception and Implicit Intersection )

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

Index will try to give us

_______    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 , rng.Rows.Count = 5		
			
	_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}"))

	  =     _B4	_D4
		_B5	_D5
		_B6	_D6
		_B7	_D7
		_B8	_D8

---

........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}")

---

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

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

---

Alan