Using Index from VBA

[jmbob666]

I recently discovered the power of the Index function. It would be very helpful if I could only get it to work from VBA. For example:

Dim rYD as range
Set rYD = Range("C3:F10")
Data = Application.WorksheetFunction.Index(rYD, 1, 4)

Will return the value of what is in row 1, column 4 of range rYD, IE the value of F3. Not a problem. I on the other hand want to return the entire 4th column of data, as shown below:

Dim rYD as range
dim Data as range
Set rYD = Range("C3:F10")
Data = Application.WorksheetFunction.Index(rYD, , 4)

Unfortunately you get "Compile Error: Argument not Optional" when you run the macro. In a spreadsheet I can get the column if I array-enter the formula. Does anyone know how to get Index to put the data into a range variable?

 

[acontrario]

Attention: I just found out that application index comes with a 65,537 size limit! Like in the good old days! Probably kept for compatibility only.

So, not a good generic solution. Better stay away from this unless for known small volumes unlikely to grow ever.
Better to use classic programming with loops & arrays. It is seems faster & much safer.