In Excel VBA as I have shown previously you can push data between 2 or more arrays. There have been a glut of examples on this website where I have moved data from one array to another. There have even been examples where I have moved only certain columns into a single array. The following method with you the INDEX formula within VBA to move data from one array to another but only move the columns which are specified. With the following method of the 6 column array (Cols A – F) only columns A, C and E will be moved from the first variant, to the second variant. The code looks a little as follows.
Sub ReadCertainColstoArray() 'Read only Cols 1,3,5 into an Array.
Dim ar as Variant
Dim var as Variant
ar = [A1:F15]
var = Application.Index(ar, [row(1:15)], Array(1, 3, 5))
Range("J1:L" & UBound(var)) = var 'Output the Array.
End Sub
The variant (ar) holds the initial data and the variant(var) accepts only columns 1, 3, 5 as stated in the Index line.
var = Application.Index(ar, [row(1:15)], Array(1, 3, 5))
So the index is doing all the heavy lifting so the only data flowing through is the ones specified (1,3,5).
The final part of the equation is to output the variant (var). This is done with the following line.
Range("J1:L" & UBound(var)) = var
It is based on the size of the variable (var). The upper bound of the Variable (15 rows) will be the cells which are filled. The following Excel file outlines the procedure with some data.
