ReDim Preserve

Preserving Values

ReDim will re-initialize the array and destroy any data in it unless you use the Preserve keyword.
Preserve copies the elements from the old array to the new array.
It is possible to resize an array without losing the existing values by using the Preserve keyword.
When you use Preserve you can only change the size of the upper bound (not the lower bound).

ReDim Preserve aCellValues(5) 

This is an expensive operation and should be used sparingly.

Partial Clearing

You can use the ReDim Preserve statement to keep only some values.

Dim myArray() As String 
ReDim myArray(1 to 4) 
Debug.Print UBound(myArray)    '4  
myArray(1) = "one" 
myArray(2) = "two" 
myArray(3) = "three" 
myArray(4) = "four" 

' removes the last 2 items, keeping the first 2 items
ReDim Preserve myArray(1 to 2) 

Debug.Print myArray(1) 
Debug.Print myArray(2) 

Never In A Loop

The ReDim Preserve statement creates a new array - and the elements of the old array get copied into the new one.
If you are running on low memory this can cause virtual memory to be used which slows down performance.

Public Sub RunThis() 
Dim starttime As Single 
Dim timeelapsed As Single 

    starttime = VBA.Timer 
    Call Slower 
'    Call Faster
    timeelapsed = VBA.Timer - starttime 
    Call MsgBox(timeelapsed & " seconds") 
End Sub 

Public Sub Slower() 
    Dim myText() As String 
    Dim lcount As Long 
    For lcount = 0 To 5000000 
        ReDim Preserve myText(lcount) 
        myText(lcount) = lcount 
    Next lcount 
End Sub 

Public Sub Faster() 
    Dim myText() As String 
    Dim lcount As Long 
    ReDim Preserve myText(5000000) 
    For lcount = 0 To 5000000 
        myText(lcount) = lcount 
    Next lcount 
End Sub