vba array - Extract 3 Columns

[Mallesh23]

Hi Team,

I am adding Range("A1:Z19") into Array. and expected ouput colulmns are Column 1,5,10
Below code extract first Column as output.I am doing it 3 times for 3 Columns.

Range("aa1").Resize(UBound(ar, 1)).Value = Application.Index(ar, 0, 1) This print single columns

Can we extract expected 3 columns in a single line. like Application.Index(ar, 0, Array(1,2,3) Can we print 3 columns.

Dim ar As Variant
ar = Range("A1:Z19").value

Range("aa1").Resize(UBound(ar, 1)).Value = Application.Index(ar, 0, 1)
Range("ab1").Resize(UBound(ar, 1)).Value = Application.Index(ar, 0, 5)
Range("ac1").Resize(UBound(ar, 1)).Value = Application.Index(ar, 0, 10)


Sub Extract_specific_Columns()

Dim i As Long
Dim j As Long
Dim myArray As Variant

'Below code works Extract 3 Columns.
With Range("A1:Z19")
   ReDim myArray(1 To .Rows.Count, 1 To 3)

    For i = 1 To .Rows.Count
        myArray(i, 1) = .Cells(i, 5).Value
        myArray(i, 2) = .Cells(i, 1).Value
        myArray(i, 3) = .Cells(i, 10).Value
    Next i

End With

Range("aa1").Resize(UBound(myArray, 1), UBound(myArray, 2)).Value = Application.Index(myArray, 0, 0)

'2 Method also works, But Can we shorten the Code in Single line.

Dim ar As Variant
ar = Range("a1").CurrentRegion.Value

Range("aa1").Resize(UBound(ar, 1)).Value = Application.Index(ar, 0, 1)
Range("ab1").Resize(UBound(ar, 1)).Value = Application.Index(ar, 0, 5)
Range("ac1").Resize(UBound(ar, 1)).Value = Application.Index(ar, 0, 10)

End Sub

Thanks
mg

 

[Fluff]

How about

   Range("AA1").Resize(UBound(Ar), 3).Value = Application.Index(Ar, Evaluate("Row(1:" & UBound(Ar) & ")"), Array(1, 5, 10))

 

[Mallesh23]

Hi Fluff,

Brilliant! It worked, nice piece of code !

Can you explain below line, also how Evaluate works. thanks in advance for help,.
Application.Index(ar, Evaluate("Row(1:" & UBound(ar) & ")"), Array(10, 5, 1))


Thanks
mg

 

[Fluff]

Code tags are for code, please do NOT use them for normal text.
The evaluate returns an array of numbers from 1 to 19 (in this case), which then enables the index to return all rows.

 

[Mallesh23]

Hi Fluff,

Thanks once again for help. learned something new , ?

Thanks.
mg

 

[Fluff]

You're welcome & thanks for the feedback.