How to Resize an Array with Redim Statement in VBA Arrarys

Resizing an Array with Redim Statement in VBA
The ReDim statement is used to size or resize a dynamic array that has already been formally declared.
For example, if you have already declared an array with an Index value of 5 and decided to change the number of Index to 6, you can do as shown in below example Macro :
Sub Redim_My_Array( )

Dim Arr(5) 

As you declared an array with index size 5 , you can store the values as follows :

Arr(0) = "Months"

Arr(1) = “Jan”
Arr(2) = “Feb”
Arr(3) = “Mar”
Arr(4) = “Apr”
Arr(5) = “May”

Msgbox Arr(0) & "-" Arr(1) & "-" & Arr(2) & "-" & Arr(3) & "-" & Arr(4) & "-" & Arr(5)

At any point of time if you want to resize your , that means if you want to increase index size , you can use Redim statement as follows.

Redim Arr(6)

Arr(0) = "Months"

Arr(1) = “Jan”
Arr(2) = “Feb”
Arr(3) = “Mar”
Arr(4) = “Apr”
Arr(5) = “May”

Arr(6) = “Jun”

Msgbox  Arr(0) & "-" Arr(1) & "-" & Arr(2) & "-" & Arr(3) & "-" & Arr(4) & "-" & Arr(5) & "-" & Arr(6)

End Sub 

Note :

Please note that when you use Redim() statement in between of the Macro , already stored values in Array will be lost , you need to restore again.To avoid this problem , you need use 'Redim Preserve' Keyword.

Please go through the below link for better understanding :

Redim Preserve Keyword

Thanks, TAMATAM