VBA - How to parcel a large selection to avoid XL Crash
 

2003

VBA code that I use works fine except for large files.

Part of the code, selects all formula cells then applies a border around a sub-set of those cells
limited by the application of a IF statement as follows:

Set MyRange = Selection.SpecialCells(xlFormulas, 23)
If Not IsError(MyRange.DirectPrecedents) Then
With MyRange.DirectPrecedents
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeLeft).Weight = xlMedium
.Borders(xlEdgeLeft).ColorIndex = 4 ' Bright Green
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeTop).Weight = xlMedium
.Borders(xlEdgeTop).ColorIndex = 4 ' Bright Green
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeBottom).Weight = xlMedium
.Borders(xlEdgeBottom).ColorIndex = 4 ' Bright Green
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeRight).Weight = xlMedium
.Borders(xlEdgeRight).ColorIndex = 4 ' Bright Green
.Borders(xlInsideVertical).LineStyle = xlContinuous
.Borders(xlInsideVertical).Weight = xlMedium
.Borders(xlInsideVertical).ColorIndex = 4 ' Bright Green
.Borders(xlInsideHorizontal).LineStyle = xlContinuous
.Borders(xlInsideHorizontal).Weight = xlMedium
.Borders(xlInsideHorizontal).ColorIndex = 4 ' Bright Green
End With

A problem occurs with large files where either the shear number of selected cells overwhelms Excel
and/or the processing involved in applying the borders to those causes Excel to crash.

My question, with VBA, how can I programmatically limit the number of cells (it might be that 8192
non-contiguous cell issue) to say groups of say 5000 cells per processing loop but at the same time
make sure that of all the desire cells are processed?

Thanks

EagleOne