Excel Copying Variable Columns with Array

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:15)], Array(1, 3, 5)) ‘Just Cols 1,3,5
Range(“J1:L” & UBound(var)) = var ‘Output the Array.
End Sub

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.

Excel Variable Columns