Working with Ranges and Arrays in Excel

Moving an array from one place to another is a useful skill to master, especially when you can move part of a range. The following procedure will move data from one sheet to another and filter out the data not required, leaving a subset of data on the new sheet. As this happens in Excel VBA memory the data moves at lightning speed.

The following YouTube video takes you through the process from this tutorial and explains some of the concepts in more detail. The following file goes with the video.

Array.xlsm

The Excel Process

The following Excel range of data will be incorporated into an array. Only the ORANGE columns will be moved from one array (ar) to the holding array (var). This will then be output to another sheet.

The VBA Array Code

In generating a procedure it is important to set up your variables, firstly the array variant that will hold the range you wish to move and secondly the array variant that will hold the truncated array. The following is the code which will move data with an array. An explanation of what is happening is below.

Sub WriteArray()

Dim ar As Variant
Dim var As Variant
Dim a As Variant
Dim i As Long
Dim j As Long
Dim n As Long

a = [{2,7,12,13}] 'Columns to be included in the array

ar = [a1].CurrentRegion

ReDim var(1 To UBound(ar), 1 To 4)

    For i = 1 To UBound(ar)
        If ar(i, 7) = "Store" Then 'Col 7 Contains the term Store in some rows.
            n = n + 1
                For j = 1 To 4
                    var(n, j) = ar(i, a(j))
                Next j
        End If
    Next i
Sheet3.Range("A2").Resize(n, UBound(a)) = var
End Sub

Setting up the code - declare two variables. One for the range of cells and one to house the partial array. In the above example only columns 2,7,12,13.

Dim ar As Variant ‘ Holds the original range of data

Dim var As Variant ‘Holds part of the array above

After the variables have been output a fresh variant (a) is used to output the columns used in the,

a = [{2,7,12,13}]

Columns 2, 7, 12,13 are held in array (a).

To slighly complicate the procedure there are 2 variables used to loop (i & j) and one variable (n) used as a counter. These control the rows (i) and the columns (j) and n is the counter for the array (var).

The VBA procedure starts by looping through the first array (ar) and pushing the data into array (var) based on Column 4 equalling the word “store”. The loop is a double loop, firstly to test if the rows equal “store”, then to iterate through the columns that need to be included in the secondary array (var).

Once the loop is complete it is about outputting the array to the other Excel sheet tab. The following plucks the array out.

Sheet3.Range("A2").Resize(n, UBound(a)) = var

Hope the above explanation was useful.

The following files goes with the video and has the completed code contained within it.

Array.xlsm