This lays out the limits pretty well:

http://support.microsoft.com/?id=177991
XL: Limitations of Passing Arrays to Excel Using Automation (Q177991)

--
Regards,
Tom Ogilvy

"keepitcool" wrote in message
...

somebody recently mentioned this:

Be aware that application.transpose and application.index fail for some
versions of excel when the number of elements exceeds 5461.
(xl2002 has been changed to support lots more.)

Sub Test()
Dim myArray As Variant
Dim myArray2 As Variant
Dim myArray3 As Variant

'just some test data in a 9x2 array
myArray = ActiveSheet.Range("a1:b9").Value
myArray2 = Application.Index(myArray, 1, 0)
myArray3 = Application.Transpose(Application.Index(myArray, 0, 1))
End Sub



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"QuocAnh" wrote:

Hello All,

I have a strange situation and I hope that this is the
appropriate forum.

I want to extract a column of data (10,000 elements) into
a 1-dimensional array using the following lines of code:

* Dim rTmp1 As Range
* Dim vTmp1() As Variant

* Set rTmp1 = Range(.Cells(1, 1), .Cells(5461, 1))
* vTmp1 = Application.WorksheetFunction.Transpose
(rTmp1.Value)

The max number of elements I can get is "5461" (don't know
why this number?), any number larger than this, the error
message is: "Type mismatch"

Has anyone ran across such a situation? and how did you
get around it?

fyi, I know that I can get all 10,000 elements if I use
the following line:
* vTmp1 = rTmp1.Value

Unfortunately, this gives me a 2-dimensional array, in the
following form: vTmp(i,1), which screws up my subsequent
calc's, and I don't really want to go back and recode the
calculation functions.

Thanks in advance

Anh.