Array size limits in Resize Transpose code

Hi all, I've got an issue regarding array size limits in Excel, which I thought was solved but unfortunately persists:

--Background--
I have the following bit of code that performs an averaging function between pairs of non-zero numbers in an array. It outputs the average value based on the number of rows that the cells were apart. Running the macro returns a column of these averages, together with a column giving the number of rows over which the average was calculated. You can see this in action in the attached file.

--Problem--
The problem I have is that I get a 'run-time error "13"' Type Mismatch when I run the code on my Mac and there are more than 91 non zero values in my array. Thanks to members of this forum, I have since discovered that the number of non zero numbers can be up to about 350 if the code is run on a PC rather than a Mac. I have since realised though that some of the arrays I have contain >1000 non zero numbers, hence the problem remains

Basically therefore, I need a way of telling the code to stop once this limit of 350 is reached. I'm not sure exactly how to implement this so any help would be gratefully appreciated

The attached file should help illustrate the problem

Please Login or Register  to view this content.

hi, carbonboywonder, basing on your example I do not understand what limitations you are talking about if your result data is returned in column. It has the only limit of number of rows of the Excel sheet: 65536 for Excel 2003 or over 1 million for Excel 2007. As soon as your original data is within the sheet limits the code will process data without any problems.
Re: Error 13 Type Mismatch - most probably it is caused by non-digit value in array that is for example divided by number.

PS. The code uses not the best methods in terms of its efficiency - Preserve is rather costly thing to use. I'm PC user.