GoTo Loop?

J

JayL

All,
I'm running in to the following problem when using Edit / GoTo - as
described by Microsoft.
How can I create the workaround described?
=======================================
CAUSE
This behavior occurs if you select more than 8,192 non-contiguous cells with
your macro. Excel only supports a maximum of 8,192 non-contiguous cells
through VBA macros.

Typically, if you try to manually select more than 8,192 non-contiguous
cells, you receive the following error message:

The selection is too large.
However, when you use a VBA macro to make the same or a similar selection,
no error message is raised and no error code is generated that can be
captured through an error handler.
WORKAROUND
To work around this behavior, you may want to create a looping structure in
your VBA macro that handles less than the maximum 8,192 cells.
 
T

Tim Zych

You can use the Areas property, For..Each, For...Next. Hard to be more
specific without knowing what you want to do.
 
J

JayL

Still trying to figure this out. Trying to run a GoTO Special Blanks and
getting a Selection Too Large error message.
Below is what MS says is the problem.
 
T

Tom Ogilvy

Reduce your base range to a range that would produce less than 8192 separate
areas.

so you if you current range is an entire column for example, work with a
quarter of the column at a time using a loop

for each cell in Range("A1,A16385,A32769,A49153")
set rng = cell.Resize(16384,1).SpecialCells(xlBlanks)
Next

if you are trying to delete rows, then you would need to go from highest to
lowest

Dim rng as Range, rng1 as Range
varr = Array(49153,32769,16385,1)
for each vVal in varr
set rng = cells(vVal,1).Resize(16384,1)
on Error Resume Next
set rng1 = rng.specialCells(xlBlanks)
On Error goto 0
if not rng1 is nothing then
rng1.EntireRow.Delete
end if
Next
 
Top