Excel VBA FOR LOOP Crashing on 1000th Time

Question

I am trying to use some VBA code to copy a range of cells and paste its values in the next empty rows 2111 times.

This pastes successfully up to the 754507 row where after this it crashes.

I can see in the debug that it stops at the 1000th loop.

Option Explicit
Sub Paste_APIROWS()

Application.ScreenUpdating = False

Dim i As Long
Range("A2:H754").Copy
For i = 1 To 2111
    Range("A2:H754" & i).PasteSpecial Paste:=xlPasteValues
    Debug.Print i
    Next i

   Application.CutCopyMode = False

End Sub

I expect in the end to have about 1589583 rows but instead appear to be only getting about half of this.

The error message I get is "Run-time error '1004': Method 'Range' of object'_Global' failed"

Any advice would be greatly appreciated.

Many Thanks.

Did you mean Range("A2:H" & CStr(754+i)).PasteSpecial Paste:=xlPasteValues — John Alexiou

Dai Dai · Accepted Answer · 2019-05-05T18:42:06

Run the loop in your head:

When i = 1, then the range is "A2:H7541" (Rows 2 through 7,541)
When i = 2, then the range is "A2:H7542" (Rows 2 through 7,542)
When i = 9, then the range is "A2:H7549" (Rows 2 through 7,549)
When i = 10, then the range is "A2:H75410" (Rows 2 through 75,410)
When i = 99, then the range is "A2:H75499" (Rows 2 through 75,499)
When i = 100, then the range is "A2:H754100" (Rows 2 through 754,100)
When i = 900, then the range is "A2:H754900" (Rows 2 through 754,900)
When i = 999, then the range is "A2:H754999" (Rows 2 through 754,999)
When i = 1000, then the range is "A2:H7541000" (Rows 2 through 7,541,000)

Notice as each value of i crosses each 10th power the row number increases by an order of magnitude:

From i = 9 to i = 10 you go from row 7,549 to 75,410
From i = 99 to i = 100 you go from row 75,499 to 754,100
From i = 999 to i = 1000 you go from row 754,100 to 7,541,000

Also note that your destination range row is always 2 - so on each iteration you're always overwriting yourself.

It crashes because Excel spreadsheets (since Excel 2007) cannot exceed 1,048,576 rows, hence the crash. The limit is 65,355 prior to Excel 2007 or when using a non-OOXML spreadsheet in modern versions of Excel).

I expect in the end to have about 1,589,583 rows but instead appear to be only getting about half of this.

Two things:

Excel does not support 1,589,583 rows anyway (as said, the maximum is 1,048,576).
Your logic does not compute copy destination ranges correctly, as per my explanation above.

The cause of your bug is the use of string concatenation (i.e. the & operator) instead of numerical addition.

You want to copy cells in the range A2:H754 some ~~2111~~ 1930 times - that means you actually want to do this:

Const sourceRowLB =   2
Const sourceRowUB = 755 ' 755 not 754 because exclusive upper-bounds are easier to work with
Dim sourceRowCount = sourceRowUB - sourceRowLB

Dim lastCopyUB = 755

Dim sourceRangeExpr = GetRangeExpr( "A", sourceRowLB, "H", sourceRowUB ) ' Will be "A2:H754"
Range( sourceRangeExpr  ).Copy

Const loopCount As Integer = 1389 ' This cannot be 2111 because ( 2111 * 754 ) exceeds the maximum row count
For i = 1 ToloopCount ' Loop 1389 times

    ' Recompute the destination range:
    Dim destRowLB As Integer
    destRowLB = lastCopyUB
    Dim destRowUB As Integer
    destRowUB = destRowLB + sourceRowCount

    Dim rangeExpression As String
    rangeExpression = GetRangeExpr( "A", destRowLB, "H" & destRowUB )

    Range( rangeExpression ).PasteSpecial Paste:=xlPasteValues

    lastCopyUB = destRowUB

Next i

Function GetRangeExpr(startCol As String, startRow As Integer, endCol As String, endRowExclUB As Integer) As String

    GetRangeExpr = startCol  & CStr( destRowLB ) & ":" & endCol & CStr( endRowExclUB  - 1 ) ' We convert endRowExclUB to an inclusive upper-bound here

End Function

Excel VBA FOR LOOP Crashing on 1000th Time

2 Answers