Appending A Dynamic Array In VBA

Answer :

Use a variable and increment it, it makes no difference if the variable is larger than the ubound when the code completes:

Sub Test() Dim myArray() As Double, X As Long X = 0 ReDim Preserve myArray(X) For Each cell In Range("Hello")     If cell <> "" Then         ReDim Preserve myArray(0 To X)         myArray(X) = cell.Value         X = X + 1     End If Next End Sub 

Change If UBound(myArray) > 0 Then to If UBound(myArray) >= 0 Then that will solve the problem.

Sub Test()     Dim myArray() As Double     ReDim Preserve myArray(0)     For Each cell In Range("Hello")         If cell <> "" Then             If UBound(myArray) >= 0 Then                 myArray(UBound(myArray)) = cell.Value                 ReDim Preserve myArray(0 To UBound(myArray) + 1)             End If         End If     Next End Sub