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 wrote:
> 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
>
>

[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 C1:D65535 because the range is one row short of the
maximum
worksheet size and does not include the entire C or D column.


--


Gary


"Myles" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
> --
> Myles
> ------------------------------------------------------------------------
> Myles's Profile:
> http://www.excelforum.com/member.php...o&userid=28746
> View this thread: http://www.excelforum.com/showthread...hreadid=571892
>

[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.

[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

--


Gary


"Myles" <[email protected]> wrote in message
news:[email protected]...
>
> 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.
>
>
> --
> Myles
> ------------------------------------------------------------------------
> Myles's Profile:
> http://www.excelforum.com/member.php...o&userid=28746
> View this thread: http://www.excelforum.com/showthread...hreadid=571892
>

[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


Myles wrote:
> 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
>
>

[Dan Hatola]

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.

"Myles" wrote:

>
> 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
>
>
> --
> Myles
> ------------------------------------------------------------------------
> Myles's Profile: http://www.excelforum.com/member.php...o&userid=28746
> View this thread: http://www.excelforum.com/showthread...hreadid=571892
>
>

[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

Alan Beban wrote:
> 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
>
>
> Myles wrote:
>> 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
>>
>>

[Alan Beban]

Dan Hatola wrote:
> 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.
--
HTH. :>)
Dana DeLouis
Windows XP, Office 2003


"Myles" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> Myles
> ------------------------------------------------------------------------
> Myles's Profile:
> http://www.excelforum.com/member.php...o&userid=28746
> View this thread: http://www.excelforum.com/showthread...hreadid=571892
>

[Dan Hatola]

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?

"Alan Beban" wrote:

> Dan Hatola wrote:
> > 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
>

[Dan Hatola]

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


"Dan Hatola" wrote:

> 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?
>
> "Alan Beban" wrote:
>
> > Dan Hatola wrote:
> > > 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
> >

[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

"Myles" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> Myles
> ------------------------------------------------------------------------
> Myles's Profile:
http://www.excelforum.com/member.php...o&userid=28746
> View this thread: http://www.excelforum.com/showthread...hreadid=571892
>

[Dan Hatola]

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




"Dan Hatola" wrote:

> 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
>
>
> "Dan Hatola" wrote:
>
> > 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?
> >
> > "Alan Beban" wrote:
> >
> > > Dan Hatola wrote:
> > > > 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
> > >

[Tom Ogilvy]

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

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

--
Regards,
Tom Ogilvy


"Alan Beban" wrote:

> Dan Hatola wrote:
> > 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
>

[Tom Ogilvy]

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 [email protected] if you would like to discuss it further.

--
Regards,
Tom Ogilvy




"Myles" wrote:

>
> 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
>
>
> --
> Myles
> ------------------------------------------------------------------------
> Myles's Profile: http://www.excelforum.com/member.php...o&userid=28746
> View this thread: http://www.excelforum.com/showthread...hreadid=571892
>
>

[Alan Beban]

Tom Ogilvy wrote:
>> that it falls foul of
> 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