Need help to read selected columns ( actually column "A" and column "Z") from a CSV into an array. I know how to read full range into an array but my data has around 30 columns from which I require data only from few columns.
Edit from comments below:
"How can I populate a single array with say Range("A1:A5") and Range("Z1:Z5")"
And:
I was looking for an fast option without iteration.
Thanks Suji
Instead of filtering the columns out, you could also combine these two arrays: maybe it's faster...
With Application
aArr = .Transpose(Range("A1:A5").Value) 'make sure the range/columns are same size
zArr = .Transpose(Range("Z1:Z5").Value)
combinedArr = .Transpose(Array(aArr, zArr))
End With
Sample range A1:Z5
Full array:
Dim arr as Variant: arr = Range("A1:Z5").Value
Single column, 1st column in below examples:
Dim arr as Variant: arr = Range("A1:A5").Value
or:
Dim arr As Variant: arr = [A1:A5]
or:
Dim rng as Range: Set rng = Range("A1:Z5")
Dim arr as Variant: arr = rng.Columns(1).Value
or:
Dim arr1 as Variant: arr1 = Range("A1:Z5").Value
Dim arr2 As Variant: arr2 = Application.Index(arr1, 0, 1)
Multiple columns - Static rows, 1st and 26th column in examples:
After your comment it became clear you want to populate a single array without looping with, for example column A and Z only. You can try the below:
Dim arr As Variant: arr = Application.Index(Range("A1:Z5").Value, [ROW(1:5)], Array(1, 26))
Multiple columns - Dynamic rows, 1st and 26th column in examples:
With Sheet1 'Change accordingly
Dim lr As Long: lr = .Cells(.Rows.Count, 1).End(xlUp).Row
Dim arr As Variant: arr = Application.Index(.Range("A1:Z" & lr).Value, .Evaluate("ROW(1:" & lr & ")"), Array(1, 26))
End With
And there are many more ways to populate an array. However, you also need to ask yourself if that's what you need. You can access all items from any of the columns A-Z from your initial array through its X and Y index.
