How to redim multidimensional correctly

[ouvay]

Hello

My code here (posting only error area) gives me subscription out of range error

For i = LBound(l) To UBound(l)
    If l(i, 6) = "Completed" Then
        n = n + 1
        ReDim Preserve a(1 To n) As Variant
        a(n) = l(i, 1)
    End If
Next i

ReDim Preserve a(1 To UBound(a), 1 To 50) As Variant

I know we cannot redim the first dimensions of an array, that is why I wrote it like this.. but unfortunately I cannot expand my array columns

any advice?

 

[johnnyL]

Found it:

Public Function ReDimPreserve(ArrayNameToPreserve, NewRowUbound, NewColumnUbound)
'
' Code inspired by Control Freak
'
' Redim & preserve both dimensions for a 2D array
'
' example usage of the function:
' ArrayName = ReDimPreserve(ArrayName,NewRowSize,NewColumnSize)
' ie.
' InputArray = ReDimPreserve(InputArray,10,20)
'
    ReDimPreserve = False
'
    If IsArray(ArrayNameToPreserve) Then                                                                    ' If the variable is an array then ...
        ReDim NewArrayNameToPreserve(NewRowUbound, NewColumnUbound)                                         '   Create New 2D Array
        OldRowUbound = UBound(ArrayNameToPreserve, 1)                                                       '   Save row Ubound of original array
        OldColumnUbound = UBound(ArrayNameToPreserve, 2)                                                    '   Save column Ubound of original array
'
        For NewRow = LBound(ArrayNameToPreserve, 1) To NewRowUbound                                         '   Loop through rows of original array
            For NewColumn = LBound(ArrayNameToPreserve, 2) To NewColumnUbound                               '       Loop through columns of original array
                If OldRowUbound >= NewRow And OldColumnUbound >= NewColumn Then
                    NewArrayNameToPreserve(NewRow, NewColumn) = ArrayNameToPreserve(NewRow, NewColumn)      '               Append additional rows/columns to NewArrayNameToPreserve
                End If
            Next                                                                                            '       Loop back
        Next                                                                                                '   Loop back
'
        If IsArray(NewArrayNameToPreserve) Then ReDimPreserve = NewArrayNameToPreserve
    End If
End Function

How is it capable of resizing both of the dimensions?

Well actually it can't. What it actually does is sets up an array with the row size and column size that you specify and copies data from the first array into it. It then sets the name of the new array to the array name that you specify. The name can be the same name as the original array name or a different array name.

 

[ouvay]

How is it capable of resizing both of the dimensions?

Well actually it can't. What it actually does is sets up an array with the row size and column size that you specify and copies data from the first array into it. It then sets the name of the new array to the array name that you specify. The name can be the same name as the original array name or a different array name.

Hey! so I managed to finally give it a spin and it works wonderfully!

I noticed it responds to

Option Base 1

so that was great!

thanks for sharing!

 

[johnnyL]

Glad to help.