My tassie friend Valario asked another interesting and engaging question. The question came from the blog post ‘Read Individual Columns to An Array’. The design of the code was a little bit static so Valerio’s question was as follows.
Another question just to ruin your night!
The following code from your blog:-
Option Explicit
Sub ReadCertainColstoArray() 'Read only Cols 1,3,5 into an Array.
Dim ar as Variant
Dim var as Variant
ar = [A1:F15] 'Static Range
var = Application.Index(ar, [row(1:1000)], Array(1, 3, 5)) 'Just Cols 1,3,5
Range("J1:L" & UBound(var)) = var 'Output the Array.
End Sub
The following YouTube video runs through the procedure to generate an output of certain columns from a master table. You choose the columns to fit into the array. The file used in the video is below.
File for Video - ArraysOutput.xlsm
Has a fixed range for the array and rows. How can these be made dynamic for varying rows? To cover a data set, I assume that the array (ar) could be : ar = Range("A1:F" & lRow) after declaring the lRow variable.
But how can this be done for the rows?
In answering the question I looked to draw on Excel's Evaluate feature to generate a flexible row and column combination. The VBA rows.count command can determine the end point of data in your range and evaluate stores this range in order for it to be used in the Index formula.
SNB has some wonderful information on his blog VBA for Smarties (great name by the way). So if you want to put your 'poindexter' hat on visit this Excel sage's site. There is a wealth of information about VBA and SNB takes a different view than most about how to trap and relocate data.
Sub ColstoArray1() 'Read only Cols 1,3,5 into an Array.
Dim ar as Variant
Dim var as Variant
ar = Range("A1", Range("F" & Rows.Count).End(xlUp)) 'Flexible starting Range
var = Application.Index(ar,Evaluate("row(1:" & [a1].CurrentRegion.Rows.Count & ")"), Array(1, 3, 5))
Range("J1:L" & UBound(var)) = var 'Output the Array.
End Sub
Effectively it works the same as the first bit of code with the added advantage of being flexible based on the length of your columns. I have made the following flexible so it works over two sheets (output sheet gets the results), this may come in handy for some people.
Sub ColstoArray2() 'Read only Cols 1,3,5 into an Array.
Dim ar as Variant
Dim var as Variant
Dim Sh as Worksheet 'Add output sheet
Set Sh = Sheet1
ar = Sh.Range("A1", Sh.Range("F" & Rows.Count).End(xlUp))
var = Application.Index(ar, Evaluate("row(1:" & Sh.[a1].CurrentRegion.Rows.Count & ")"), Array(1, 3, 5))
Range("B10:D" & UBound(var) + 9) = var 'Output the Array.
End Sub
So there you have it the variable VBA array which lets you trap columns of your choice based on a moving range. It is not for the faint hearted but it works wonderfully, thanks again for the question Valerio!!!
In a veritable celebration of the above here is the file to show workings. Knock yourself out cold.
