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
>