It might just be of passing interest to add here, that the “slicing” technique demonstrated by Hans in his last code is an interesting case where you can get .Index to return you a Range Object. In most cases
.Index returns a value or Array of values. By default, as in the case of the code example used by Han’s, the “Slicing” technique will also return an Array of values.
But by carefully
Dim ing variables, you can get the code to return a Range Object:
The following code does the same as Han’s code. But it returns a Range.
Not only is this advantageous in allowing you to have Full Range Objects returned, but also, even if you are finally interested in only values, the following code allows you to overcome the various size limits and other problems associated with Arrays used in Worksheet Functions such as
.Index
Code: Select all
Sub TestRngversion() ' http://www.eileenslounge.com/viewtopic.php?f=30&t=25517
Dim arr
Dim i
Dim j As Long
Dim n As Long
Dim Rngarr As Range
Set Rngarr = Sheets("Sheets1").Range("A1").CurrentRegion
Let n = Rngarr.Rows.Count
For Each i In Array(2, 7)
j = j + 1
Dim rngReturn As Range
Set rngReturn = Application.Index(Rngarr, 0, i)
Sheets("Sheet2").Cells(1, j).Resize(n) = rngReturn
Next i
End Sub
'
' Rem Ref Slicing: https://usefulgyaan.wordpress.com/2013/06/12/vba-trick-of-the-week-slicing-an-array-without-loop-application-index/#comment-587
' Rem Ref Array problems when using worksheet functions: http://excelmatters.com/2016/03/08/transpose-bug-in-2013-and-2016/
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also