Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
GoTo Loop?
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
GoTo Loop?
You can use the Areas property, For..Each, For...Next. Hard to be more
specific without knowing what you want to do. "JayL" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
GoTo Loop?
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. "Tim Zych" wrote in message ... You can use the Areas property, For..Each, For...Next. Hard to be more specific without knowing what you want to do. "JayL" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
GoTo Loop?
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 -- Regards, Tom Ogilvy "JayL" wrote in message ... 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. "Tim Zych" wrote in message ... You can use the Areas property, For..Each, For...Next. Hard to be more specific without knowing what you want to do. "JayL" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
? IF & GOTO ? | Excel Worksheet Functions | |||
? IF and GOTO ? | Excel Worksheet Functions | |||
On Error GoTo Label in a loop only working once. | Charts and Charting in Excel | |||
VBA - using Goto - Please Help! | Excel Programming | |||
HELP!!!! Can't stop a loop (NOT an infinite loop) | Excel Programming |