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