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 
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
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 & ")")
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
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
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.
Code:
= { 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 )
Code:
= { 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
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
Code:
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}"))
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}")
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
Alan
Reply With Quote
Bookmarks