Help with array's and ranges

[Ragnar1211]

I am trying to learn something about using arrays with excel ranges.

The following code works fine, it takes the data in the range and feeds it into an array then it puts that data somewhere else:

sub array2test()
dim ar1

ar1 = range("a2:b10")

range("L2:M10") = ar1

end sub

However, I cannot figure out how to put a part of the array into a range without looping:

sub array2test()
dim ar1

ar1 = range("a2:b10")

range("M2:M10") = ar1  '<-- I only want the second column of ar1, but don't know how to get it

end sub

Question - how do you get the second column of an array without looping through it?

[/code]

 

[Ragnar1211]

Any thoughts?

 

[pgc01]

Hi Gerry

Try:

Sub array2test()
Dim ar1
 
ar1 = Range("a2:b10")
 
'v-- I only want the second column of ar1, but don't know how to get it
 
Range("M2:M10") = Application.WorksheetFunction.Index(ar1, 0, 2)
 
End Sub

 

[Ragnar1211]

Thanks!

Are there other ways to do this without using the Index function? Just curious.

 

[pgc01]

Are there other ways to do this without using the Index function? Just curious.

I don't know. Vba is very poor as far as this type of array manipulation is concerned. I think you do have to use worksheet functions if you don't want to use a loop.

 

[tusharm]

Use a loop to transfer data from column 2 of the matrix to a new 1D array. Then, transfer that to the worksheet.

In memory transfers are very fast. It's the transfer to worksheet one-cell-at-a-time that is painfully slow.

Thanks!

Are there other ways to do this without using the Index function? Just curious.