Thanks again Rowan.

We learn something everyday, don't we? Following the revelation, using
conventional (as opposed to Variant) Array in my example removes any
danger as the following code illustrates:

Sub m()
Dim a()
Dim b()

Range("a1") = 100
'Range("a2") = 500 *'commented out*
num = Application.CountA([a:a])


For i = 1 To num
ReDim Preserve a(i)
a(i) = Cells(i, 1)
Next

Range("a1:a2").Clear
Range("a1") = 92
'Range("a2") = 800 *'commented out*

For i = 1 To num
ReDim Preserve b(i)
b(i) = Cells(i, 1)
Next

For i = 1 To num
MsgBox a(i) - b(i) 'NO ERROR returned
Next

End Sub


The mighty lesson I have learnt in all this is that uncritical use of
the Variant Array is fraught with danger *where the defined range is
unstable*.
Variant Array - a banana peel if ever there was one!


David.


--
davidm
------------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=483026