How To Add Filtered Column In Array Vba

Excel VBA Arrays

In VBA, an array is a retentiveness location which is capable of storing some value or more than 1 value in it. There are iii types of arrays in VBA, Ane Dimension, Ii Dimensions and Multi Dimensions. One dimension uses ane index, such single category of age grouping, Two Dimension uses two indexes such as people and their ages, and Multi Dimension array has more than ii indexes such as surface height at dissimilar levels. We can declare Static and Dynamic variables in Excel for Array.

How to Utilise Excel VBA Arrays?

Let's sympathise how to use Excel VBA Arrays and their types with some examples.

Y'all can download this VBA Arrays Excel Template here – VBA Arrays Excel Template

Case #i

Take a look at the below example. X is a variable which holds the information blazon of integer.

Lawmaking:

              Sub              Array_Example1()              Dim              x              As Integer              x = 1              End Sub

Now assign a value of 1 to the declared variable x.

Let's insert the value of 1 to the prison cell A1.

Code:

              Sub              Array_Example1()              Dim              ten              As Integer              ten = ane  Range("A1").Value = x              Cease Sub

The x value is equal to 1, and in the range, A1 the value will be inserted every bit the value of 10, i.e. the value of x is 1. Now run the code using the F5 central or manually to see results.

In the to a higher place example, x holds merely one variable; that'due south all. But if I desire to insert five consecutive numbers by using the single variable, I need to apply the type array variable, which can hold many variable values in a unmarried variable name.

Example #ii

At present have a expect at the below case. A variable proper name is 10, and the information blazon is LONG. But while declaring the variable itself, I have opened the bracket and mentioned ane to 5. This means variable x will concur v unlike types of values.

Lawmaking:

              Sub              Array_Example()              Dim              x(1              To              v)              Equally Long, i              As Integer              End Sub

After that, I have assigned the values to each variable. 10(1) = xx ways the first variable should be equal to the value of 20. X(2) = 25 means the 2nd variable should be equal to the value of 25 so on.

Lawmaking:

              SubArray_Example()                              Dim10(one              To              5)              Equally Long, i              As Integer              10(one) = xx     x(2) = 25     x(3) = 44     x(4) = 78     x(five) = 96              Terminate Sub

Later I accept declared one more than variable called "I" this is the second type of a variable and holds the information blazon of integer.

In the side by side stride, I have applied FOR loops to insert assigned numbers to the array in the first column. I have set the value of the variable ito 1, and I take instructed the loop to run for i to v times. When the loop is running for the get-go fourth dimension i value will exist equal to 1. CELLS (I,1).value = x(i) this means for the first time i is equal to 1 i.eastward. CELLS(1,i).value = x(i), in the get-go row first column (Jail cell A1) the value will be the first assortment (ten(i)) value i.e. xx.

When the loop runs for the second time i value becomes ii i.due east. CELLS (2, ane).value = 10 (ii), in the second row first column (A2) the value will exist the 2nd array ( x(two) ) value i.due east. 25.

Lawmaking:

              Sub              Array_Example()              Dim              10(1              To              five)              As Long, i              As Integer              x(1) = xx x(2) = 25 x(3) = 44 x(4) = 78 x(5) = 96              For              i = i              To              five  Cells(i, 1).Value = x(i)              Next              i              End Sub

Like this, when the loops keep running, the values will keep irresolute. When the loops run for the 3rd time A3 cell value will be 44, the fourth-time loop runs A4 prison cell value will be 78 when the loops run for the last time or fifth time A5 cell value volition exist 96.

After running the code using the F5 primal or manually, nosotros will get results as shown below.

Types of Arrays in Excel

Arrays have different types in VBA. In that location are v types of arrays bachelor in excel.

Static Array
Dynamic Array
One Dimensional Array
Two Dimensional Array
Multi-Dimensional Array

Static Array

In this type of array, the length of the array is pre-determined in accelerate and remains constant.

Code:

              Sub              Static_Example()              Dim              ArrayType(ane              To              3)              Every bit Integer              ArrayType(ane) = 0 ArrayType(two) = 1 ArrayType(iii) = 2  Cells(i, 1).Value = ArrayType(one) Cells(1, ii).Value = ArrayType(2) Cells(1, 3).Value = ArrayType(3)              Finish Sub

In the above code, ArrayType length is determined well in advance as 1 to 3, and the data blazon is Integer.

Later running the lawmaking using the F5 key or manually, we will get results as shown below.

Dynamic Array

In this type of array, the length of the array is not pre-determined well in accelerate.

Code:

              Sub              Dynamic_Example()              Dim              ArrayType() As Variant              ReDim              ArrayType(3)  ArrayType(1) = "My Name" ArrayType(2) = "is" ArrayType(3) = "Excel"  Cells(1, ane).Value = ArrayType(1) Cells(1, two).Value = ArrayType(2) Cells(1, 3).Value = ArrayType(iii)              Stop Sub

In this type of array, data is Variant, and the length is not determined hither. Subsequently declaring the variable, I have assigned the length of the assortment by using the ReDim part. This array will insert the values similar this Cell A1 = My Name, Prison cell B1 = is Prison cell C1 = Excel.

One Dimensional Array

In this type of array, the length is determined, only in i dimension, it works.

Lawmaking:

              Sub              One_Dimensional()              Dim OneDimension(i              To              3) Every bit String  OneDimension(1) = xl OneDimension(ii) = fifty OneDimension(3) = 15              End Sub

Show these value in VBA Message Box.

Code:

              Sub              One_Dimensional()              Dim OneDimension(1              To              3) As String  OneDimension(i) = xl OneDimension(2) = l OneDimension(three) = fifteen  MsgBox OneDimension(1) & "," & OneDimension(two) & "," & OneDimension(3)              Cease Sub

Run this code using the F5 key or manually, and nosotros will get the following upshot.

Ii Dimensional Assortment

In this type of assortment, the length is determined in two dimensions, and it works.

Code:

              Sub              Two_Dimensional()              Dim              TwoDimension(1              To              2, i              To2)              Every bit Long              Dim              i              As Integer              Dim              j              Every bit Integer              TwoDimension(i, 2) = forty TwoDimension(2, i) = 50 TwoDimension(one, 1) = 15 TwoDimension(2, 2) = ten              End Sub

Now to store these values to the cells beneath lawmaking.

Code:

              Sub Two_Dimensional()                              Dim              TwoDimension(1              To              2, 1              To              ii)              As Long              Dim              i              As Integer              Dim              j              As Integer              TwoDimension(1, 2) = xl TwoDimension(ii, 1) = fifty TwoDimension(1, 1) = 15 TwoDimension(2, 2) = 10              For              i = one              To              2                              For              j = 1              To              ii         Cells(i, j) = TwoDimension(i, j)                              Next                            j              Next                            i              End Sub

This will store the information similar below.

Multi-Dimensional Array

In this type of array, the length is determined, but in multi-dimension, information technology works.

Code:

              Sub              Multi_Dimensional()              Dim              TwoDimension(ane              To              3, 1              To              2)              As Long              Dim              i              As Integer              Dim              j              Equally Integer              MultiDimension(1, 1) = 15 MultiDimension(1, ii) = 40 MultiDimension(ii, i) = l MultiDimension(2, two) = 10 MultiDimension(3, 1) = 98 MultiDimension(3, 2) = 54

If you wait at the above code, firstly, I have declared the assortment every bit 1 to iii then i to 2. This means when I am writing the array, firstly, I tin use simply 1 to three numbers, just in the 2d space, I tin apply but 1 to 2, non 1 to 3.

Using Loop, we tin can insert values in cells. I have used two loops for a multi-dimensional assortment.

Code:

              Sub              Multi_Dimensional()              Dim              TwoDimension(1              To              3, 1              To              2)              As Long              Dimi              Every bit Integer              Dim              j              As Integer              MultiDimension(1, i) = fifteen MultiDimension(i, 2) = twoscore MultiDimension(two, 1) = fifty MultiDimension(ii, 2) = ten MultiDimension(3, 1) = 98 MultiDimension(three, ii) = 54              For              i = 1              To              three                              For              j = 1              To              2         Cells (i, j) = MultiDimension (i,  j)                              Adjacent              j              Nexti              End Sub

Later running the code using the F5 key or manually, we will go results as shown below.

Things to Think

An array volition count the values from zero, not from 1.
Array (0, 0) means first-row first column.
This excel macro file needs to be saved as a macro-enabled workbook.
In the case of a Dynamic array, we need to assign the value of the assortment by using the REDIM function in VBA.