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