redimension multidimensional arrays the first value

[kylefoley76]

There has to be a simpler way to do this. I took the advice of one poster on this forum who said that I have to set my multidimensional array to a high number then redimension it to a lower number. But in order to get it to the right number I have to run it through two loops where it seems like there has to be a simpler way to do things. So I have the array ancestors which has several blanks in it which I'm trying to get rid of. The second dimension will always be 2. I first run it through a loop to determine the ubound of it. And I call that ancestors3. Then I run the ancestors3 array through a loop and populate the ancestors2 array.

    For s = 1 To UBound(ancestors, 1)
    temp_ancest = ancestors(s, 1)
    If temp_ancest <> "" Then
        uu = uu + 1
        ReDim Preserve ancestors3(uu)
        ancestors3(uu) = temp_ancest
    End If
    Next
    
    Dim ancestors2()
    ReDim ancestors2(UBound(ancestors3), 2)
    
    For s = 1 To UBound(ancestors3, 1)
    temp_ancest = ancestors(s, 1)
    temp_ancest2 = ancestors(s, 2)
    If temp_ancest <> "" Then
        y = y + 1
        ancestors2(y, 1) = temp_ancest
        ancestors2(y, 2) = temp_ancest2
    End If
    Next

 

[iliace]

Every time you do a ReDim Preserve, you are deleting the existing array and recreating it. This is a time-consuming operation for the processor. What I recommend instead is an incremental resizing that's based on your data. This applies to the first For loop you have.

Dim iSize As Long
Dim uu As Long ' assuming you have this somewhere above, probably won't need this
Const iIncr As Long = 50


iSize = iIncr
uu = 0


ReDim ancestors3(1 to iSize)


For s = 1 To UBound(ancestors, 1)
    temp_ancest = ancestors(s, 1)
    If temp_ancest <> "" Then
        uu = uu + 1
        if (uu > iSize) Then
            iSize = iSize + iIncr
            ReDim Preserve ancestors3(1 to iSize)
        End If
        ancestors3(uu) = temp_ancest
    End If
Next s


If uu > iSize Then
    iSize = uu
    ReDim Preserve ancestors3(1 to iSize)
End If

So, you adjust your array size in increments of 50. You can change this within the code. If, by the end of the code, your array is bigger than it needs to be, you size it down. This way, you're not doing the memory allocation at each step of the For loop, but only at the increments. This will speed up your execution time. Change the 50 to whatever number you need (e.g. 10, 500, 10000) depending on the anticipated size of your array and the fluctuation.