Redimension the first value of a multi-dimensional array in VBA Excel

Vote count:

0

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

asked 3 mins ago

user147178

1

Redimension the first value of a multi-dimensional array in VBA Excel