In xl2003, it isn't a problem. Here is an alternate approach that should
work in xl97/2000

Sub AdjustArray11()
Dim ar As Variant, v As Variant
Dim i As Long, j As Long, k As Long
ReDim ar(1 To 6000, 1 To 5)
' Build a test array with some empty elements in the first column
For i = 1 To 6000
If Rnd() < 0.5 Then
For j = 1 To 5
ar(i, j) = Int(Rnd() * 100 + 1)
Next
End If
Next i
Range("A1:E6000").Value = ar
j = 0
For i = LBound(ar, 1) To UBound(ar, 1)
If Not IsEmpty(ar(i, 1)) Then
j = j + 1
End If
Next
ReDim v(1 To j, 1 To 5)
j = 0
For i = LBound(ar, 1) To UBound(ar, 1)
If Not IsEmpty(ar(i, 1)) Then
j = j + 1
For k = 1 To 5
v(j, k) = ar(i, k)
Next
End If
Next
ar = v
Range("G1").Resize(UBound(ar, 1), UBound(ar, 2)).Value = ar
Erase v
End Sub


one more thing,
For i = 1 To 20
If Rnd() < 0.5 Then
For j = 1 To 5
ar(i, j) = Int(Rnd() * 100 + 1)
Next
End If
Next i

I don't understand above lines.
If you can explain a little bit, that would be prefect!

Those lines just generate a test array that has blank rows in it. That is
just for demonstration purposes.

--
Regards,
Tom Ogilvy



"xiang" wrote in
message ...

Hi, Tom
thanks for your suggestion.
But I'm afraid that Transpose approach won't work when the elements of
array
exceed 5160. BTW, I'm using excel 2000, and I don't know what will
happen in execl 2003.

Could you offer another approach?

one more thing,
For i = 1 To 20
If Rnd() < 0.5 Then
For j = 1 To 5
ar(i, j) = Int(Rnd() * 100 + 1)
Next
End If
Next i

I don't understand above lines.
If you can explain a little bit, that would be prefect!


--
xiang
------------------------------------------------------------------------
xiang's Profile:
http://www.excelforum.com/member.php...o&userid=29489
View this thread: http://www.excelforum.com/showthread...hreadid=496567