Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

Re-size Dynamic Arrays

Sometimes when we create dynamic arrays, we know how many elements our array will have. If we know that we can specify the upper bounds of the arrays in the Redim statement. But there are times we can't tell exactly how many elements our array will have. Because we might need to add new elements to our array when go through the subroutine. I will explain this using below example.

Consider this sample data.
























Think we need to add records of each person to an array if that person get  more than $7000 of monthly income. So we don't know how many people will be added to our array when we defining it.
Because items will be added to the array when go through the subroutine. So inside a for each loop,  it will check the monthly income of each person and if it is more than $7000, records of that person will be added to the array. So below is the full code to do that.

Dim WS As Worksheet
Dim Rng As Range
Dim PeopleWithHigherIncome() As Variant
Dim HigherIncomeCounter As Long
Dim LoopCounter As Integer

Set WS = Worksheets("Sheet1")

'find last row
Lastrow = WS.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row

HigherIncomeCounter = 0

For Each Rng In WS.Range("A2", "A" & Lastrow)

    If Rng.Offset(0, 3).Value > 7000 Then
   
        HigherIncomeCounter = HigherIncomeCounter + 1
       
        ReDim Preserve PeopleWithHigherIncome(1 To 4, 1 To HigherIncomeCounter)
       
        For LoopCounter = 1 To 4
            PeopleWithHigherIncome(LoopCounter, HigherIncomeCounter) = Rng.Offset(0,                        LoopCounter - 1).Value
        Next LoopCounter
       
    End If
   
Next Rng

'Put PeopleWithHigherIncome array to new worksheet
Worksheets.Add

ActiveSheet.Range("A1", Range("A1").Offset(3, UBound(PeopleWithHigherIncome, 2) - 1)).Value = PeopleWithHigherIncome

Last two lines of above code will add new sheet and display the result array in that new worksheet.

Here is the final result you will get.










As you can see the values are transposed from our original data. In a next post I will explain how to transpose an array. So we can transpose this result array before put it to a worksheet.

Also there are few things you need to remember.

You can't use below line instead of ReDim Preserve PeopleWithHigherIncome(1 To 4, 1 To HigherIncomeCounter)

ReDim PeopleWithHigherIncome(1 To 4, 1 To HigherIncomeCounter)

Because if you use only ReDim keyword, it will delete earlier added items to the array. But ReDim Preserve keyword will add new items to the array without deleting existing items.

Also you can't write



ReDim Preserve PeopleWithHigherIncome(1 To HigherIncomeCounter, 1 To 4)

Instead of

ReDim Preserve PeopleWithHigherIncome(1 To 4, 1 To HigherIncomeCounter)

If you add Preserve keyword, it can only Re-dimension the last dimension of the array.


Contact Form

Name

Email *

Message *

Popular Posts