Array size limit

[Myles]

I have an array dimensioned to load up as many qualifying rows as exist
on a worksheet. That is to say, possibly up to 65536 variables. The
problem with the code, as might be expected, is that it falls foul of
Excel's (2000) array limit of 5460 and therefore crashes midstream.

Does anyone know of such workaround as coild be applied to a code
sructure like the following?

Sub FindNthFarthermostPopulatedColumn()
Dim arr() As Long
Dim i As Long,k as long
Dim x%

For i = 1 To 65536
If Application.CountA(Rows(i)) > 0 Then
k = k + 1
ReDim Preserve arr(k)
arr(k) = Cells(i, "IV").End(xlToLeft).Column
End If
Next

x = Application.Large(arr, 3) 'for 3rd farthest pop. column counting
intervening blank columns
MsgBox "Column No. is " & x

End Sub

The above code chugs along only to hit a bump at k=5461, assuming the
populated rows extend that far and beyond.


Myles

 

[Alan Beban]

How did you decide the problem was at k=5460? Change the 65536 to 65535
and see if you still believe the problem is at 5460.

Alan Beban

 

[Myles]

Alan,

I altered 65536 ro 65535 with no relief.

The 5460 limit was ascertained by trial and error-of course aided by
the KB observation of such a ceiling.

May be I will have to wait for the arrival of EXCEL 12 post beta.


Myles

 

[Gary Keramidas]

not sure if this is what you want or not

Worksheet arrays Limited by available memory. Also, arrays cannot refer to
entire columns. For example, an
array cannot refer to the entire column C:C or to the range C1:C65536. However,
an array
can refer to the range C165535 because the range is one row short of the
maximum
worksheet size and does not include the entire C or D column.

 

[Myles]

Gary:

Useful observation about the use of 65535 - one less than the ful
complement. But the issue transcends that. It is declared that element
in an array cannot exceed 5460 which thus presents a hurdle.

Many thanks though

 

[Myles]

Gary:

Useful observation about the use of 65535 - one less than the full
complement. But the issue transcends that. It is declared that elements
in an array cannot exceed 5460 which thus presents a hurdle.

Many thanks though.

 

[Gary Keramidas]

i was just wondering if it was the amount of memory limiting you to that number
of elements

 

[Myles]

i was just wondering if it was the amount of memory limiting you to that
number of elements

Yes and No! Yes because there is an inherent constraint in Excel which
does not allow you to exceed that magic number of 5460. In that wise,
Excel's resources put a break on how far you can go.

On the other hand, as you well know, there are instances where an
operation is not subjected to any computational limitation per se
except of course limitation imposed by available "system resources"
which vary from one window version to another, to say nothing of
varying hardware specifications.
The Array limit is a defined limit and is not governed by overall
resource bank


Myles

 

[Alan Beban]

I'm really curious on this one. What version of Excel are you using?
Also, precisely what is the KB observation? Your code includes a VBA
array, not a worksheet array. I am using Excel 2002. The following
seems to work fine for me, with Row 5600 containing the values in the
first 9 columns, with the 9th column being the 3rd farthest populated one:

Sub FindNthFarthermostPopulatedColumn()
Dim arr() As Long
Dim i As Long, k As Long
Dim x%
ReDim arr(65535)
For i = 1 To 65535
If Application.CountA(Rows(i)) > 0 Then
k = k + 1
arr(k) = Cells(i, "IV").End(xlToLeft).Column
End If
Next

x = Application.Large(arr, 3) 'for 3rd farthest pop. column counting
'intervening blank columns
MsgBox "Column No. is " & x

End Sub

Alan Beban

 

[Alan Beban]

There is no specified limit on the number of elements in a VBA array. In
version xl2000 and previous there is a limit (the 5461 limit) on the
size array that can be transferred to and from a worksheet, and on the
size array on which certain worksheet functions can operate(e.g.,
TRANSPOSE, INDEX, etc.). I don't see your code as involving those
circumstances, and that's why I find the reference to a 5461-element
limit puzzling.

Alan Beban

 

[Guest]

Myles,
The array limitation of which you speak is a limitation that occurs only
when you are trying to pass arrays to the worksheet. In this case you are
running into problems because you are trying to use the "Large" function (a
worksheet function) on the array. In your example it appears that you are
setting x equal to the result of the large function in an effort to determine
the row with the nth largest number of values. I am pretty sure that large
will only get you the count of numbers in the nth largest row rather than the
row number itself. For that you could use a match and index functions (if
you didn't have to worry about the array limitation).

A work around might be possible, but it would help to better understand
exactly what you are trying to accomplish. Does the macro really need to be
able to find the nth largest value or can it be limited to some range (e.g. 1
to 10)? Do you want to return the row number, the count of items in the row,
or both?

My first two thoughts were that you could determine how many rows you have
to start (count them) and create a two dimensional array that was 5460 by
(rows/5460 + 1). For each column of the array you could bring back the nth
largest value/row and then combine the results, pulling the nth largest from
the combined result set.

Another option would be to create a second array that keeps track of the nth
largest values as you count each line. Each time it finds a value greater
than n, it will insert it into the array in the appropriate location and then
shift the values below it by one spot (last one drops off).

Once I better understand exactly what you need to do I can send some code.

 

[Alan Beban]

Myles,
The array limitation of which you speak is a limitation that occurs only
when you are trying to pass arrays to the worksheet. In this case you are
running into problems because you are trying to use the "Large" function (a
worksheet function) on the array. In your example it appears that you are
setting x equal to the result of the large function in an effort to determine
the row with the nth largest number of values. . . .

The original post didn't indicate that the poster was looking for a row
number at all, rather the column number of the 3rd "farthest" rightmost
entry in any row.

It might be that in version xl2000 and prior the LARGE function will not
operate on an array with more than 5461 elements; I can't check that
because I no longer have xl2000 on my machine. It will be helpful if the
original poster indicates which version of Excel he is using.

Alan

 

[Dana DeLouis]

You code worked ok for me also.
One small idea would be to search 256 columns, vs. 64000 Rows.

Sub Demo()
Dim Col As Collection
Dim C As Long
Dim n As Long

Set Col = New Collection
ActiveSheet.UsedRange
For C = 1 To Cells.SpecialCells(xlCellTypeLastCell).Column
n = WorksheetFunction.CountA(Columns(C))
If n > 0 Then Col.Add C, CStr(C)
Next
MsgBox Col(Col.Count - 2)
End Sub

There are other ways with "Find", but this was quick n dirty.

 

[Guest]

If the nth largest column is all that is desired, wouldn't it be possible to
loop from column IV to column AA and count the specialcells(xlconstants) in
each column until the nth number or greater was reached?

 

[Guest]

Here are two versions of what I had in mind. The first loops from the last
column to the first as I described in my previous post. It is logical to me,
but slow if the columns are more empty than full. The second loops from the
first to the last column. It is probably the way I would go. Hope this
helps.

Sub FindNthFarthermostPopulatedColumn()

Const NLarge As Long = 3 'Desired Nth largest value
Dim i As Long, Total As Long
Dim sh As Worksheet

Set sh = ActiveSheet
For i = sh.Columns.Count To 1 Step -1 'Loop from the last column to the
first
On Error Resume Next 'Avoid errors when the column is empty
Total = sh.Columns(i).Cells.SpecialCells(xlConstants).Count 'Counts
the constants in the column
If Total >= NLarge Then Exit For 'Stop when the nth farthest
populated column is found
Next i
On Error GoTo 0
MsgBox "Column No. is " & i
End Sub


Sub FindNthFarthermostPopulatedColumn2()

Const NLarge As Long = 3 'Desired Nth largest value
Dim i As Long, Total As Long
Dim sh As Worksheet

Set sh = ActiveSheet
For i = 1 To sh.Columns.Count 'Loop from the first to the last column
On Error Resume Next 'Avoid errors when the column is empty
Total = sh.Columns(i).Cells.SpecialCells(xlConstants).Count 'Counts
the constants in the column
If (Total < NLarge) Or (Err <> 0) Then Exit For 'Stop when the nth
farthest populated column is found or remaining columns are empty
Next i
On Error GoTo 0
MsgBox "Column No. is " & i - 1
End Sub

 

[PY & Associates]

On limit of 5460, we run the following test without error please.

Sub testarray()
Dim arr() As Integer
Dim i As Long

For i = 1 To 60000
ReDim Preserve arr(i)
arr(i) = Int(Rnd() * 265 + 1)
Cells(i, 1) = arr(i)
Next i
Cells(1, 2) = Application.Large(Range(Cells(1, 1), Cells(65536, 1)), 3)
End Sub

 

[Guest]

One last revision... it only looks at columns with data...

Sub FindNthFarthermostPopulatedColumn3()

Dim NLarge As Long 'Desired Nth largest value
Dim LastCol As Integer
Dim i As Long, Total As Long

NLarge = 3
LastCol = Cells(1, 1).CurrentRegion.Column + Cells(1,
1).CurrentRegion.Columns.Count - 1 'Find last column w/ data
For i = LastCol To 1 Step -1 'Loop from the last column to the first
Total = ActiveSheet.Columns(i).SpecialCells(xlConstants).Count
'Counts the constants in the column
If Total >= NLarge Then Exit For 'Stop when the nth farthest
populated column is found
Next i
MsgBox "Column No. is " & i
End Sub

 

[Guest]

that it falls foul of
Excel's (2000) array limit of 5460

I would suspect the OP is saying he is using xl2000.

 

[Guest]

Myles,
you are correct about a limitation existing for 5461 elements as Alan has
confirmed and especially since you are running into it.

The limitation appears to be on the use of the Large worksheet function.

I would suggest writing your own function to get this information. Looping
through an array is extremely fast - light years faster than looping through
cells. However, what you are doing would require the cell looping. So . . .


a change in technique would probably be beneficial.

contact me at (e-mail address removed) if you would like to discuss it further.

 

[Alan Beban]

Excel's (2000) array limit of 5460

I would suspect the OP is saying he is using xl2000.

Perhaps. But I was puzzled by his comment

"May be I will have to wait for the arrival of EXCEL 12 post beta."

From that I assumed that he had at least xl2002.

Maybe he'll post and tell us what version he's using.

Alan Beban