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.
Thanks
mg
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.
Rich (BB code):
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