Hi Charles,

good question. In the end it might mean,
how to get rid of a once declared variable,
or how to free before allocated memory.

Don't know much about Excel.
But to redim an array of 10000000 strings
takes about 7 seconds here and now.
For only 1000000 strings, it takes 0.7 seconds.
Seems to be linear.

Not that the array wasn't dimensioned at all,
but redimensioned from the start.
Otherwise redim fails.

Sub Test056()
Dim t As Double ' time
Dim lCnt As Long ' a logn counter
ReDim sArr(1 To 1000000) As String
For lCnt = 1 To 1000000
sArr(lCnt) = Format(lCnt, "0000000")
Next
t = Timer
ReDim sArr(0)
MsgBox Format(Timer - t, "00.000") ' 0.7
End Sub

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"