If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, you might
want to consider the ArrayTranspose function.

Alan Beban

Paul Stevens wrote:
Hi Dana

Thanks for digging this out. Inconvenient but I'm pleased
to have discovered now rather than later.

If I understand KB177991 correctly, the limitations appear
the same in XL97 and XL2000. Or, regarding the 5461 limit
(footnote F), perhaps it's only the Transpose function
that is a problem in XL2000 but there are others such as I
described in XL97, anyone know!

I assume, although not yet tried, I could use two single
column arrays and adapt your code accordingly, and extend
my current limit from 2730 to say 5461.

Regards,
Paul


-----Original Message-----
Hi Paul. The 5461 is the "Other" limit for Excel 97.

Here is a link...

XL: Limitations of Passing Arrays to Excel Using

Automation

http://support.microsoft.com/default.aspx?kbid=177991

It says in part...

"The maximum number of elements in the array is limited

by available memory

or the Excel worksheet maximum size (65536 rows X 256

columns). However, the

maximum number of elements in the array that you can pass

to Excel using the

Excel Transpose function is 5461. If you exceed this

limit, you receive the

following error message: "

I don't know why it only mentions "Transpose." It fails

on other things as

well.
HTH.

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"Paul Stevens"

wrote in message

...

Hi Dana,

I'm using Excel97 / XL8. Old but not as old as XL7 as
referred to in the KB article, I can't yet find anything
similar relating to XL8.

I was a bit misleading in the way I reported the error.
Maximum array elements appears limited only by normal xl
constraints and memory, can be populated in a variety of
ways and can be dumped into cells by looping, several

10's

of k's of data no problem.

However in your demo subs following fails (no doubt some
other operations will fail also):
= .Index(myarray, 0, 1)
if there are more than 5461 in an array of one column,
2730 in each of two columns, 1365 in four, etc. So the
absolute limit in my XL8 appears to be 5461, remarkably
close to 5458 as reported for XL7.

I need to get to the bottom of this for other reasons

and

will look into it more next week. However if in the
meantime you or anyone can shed any more light I would

be

very grateful.

Regards,
Paul

<snip


.