CODEWITHSUNDEEP
excelvba
Jordan A.F.
Jordan A.F.

Reputation: 11

Excel VBA FOR LOOP Crashing on 1000th Time

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.

Upvotes: 1

Views: 387

Answers (2)

John Alexiou
John Alexiou

Reputation: 29244

Here are some hints:

  1. There is no need to do string math like Range("A2:H754" & i). A better solution is starting from the top left cell use .Cells(row, column) method to access a specific cell.
  2. Expand a cell into a table using the .Resize(row_count, column_count) method.
  3. Finally, there is no need to use the clipboard with the .Copy or .Paste methods are this is slow and memory intensive. Use direct assignment into the .Value property.

For example, to copy the 178th row from a table of 1000×8 cells located under A2 into the first row of the sheet, use the following

Range("A1").Resize(1,8).Value = Range("A2").Cells(178,1).Resize(1,8).Value

Note that the .Resize() values much match on both sides of the assignment.

Upvotes: 1

Dai
Dai

Reputation: 155065

Run the loop in your head:

  1. When i = 1, then the range is "A2:H7541" (Rows 2 through 7,541)
  2. When i = 2, then the range is "A2:H7542" (Rows 2 through 7,542)
  3. When i = 9, then the range is "A2:H7549" (Rows 2 through 7,549)
  4. When i = 10, then the range is "A2:H75410" (Rows 2 through 75,410)
  5. When i = 99, then the range is "A2:H75499" (Rows 2 through 75,499)
  6. When i = 100, then the range is "A2:H754100" (Rows 2 through 754,100)
  7. When i = 900, then the range is "A2:H754900" (Rows 2 through 754,900)
  8. When i = 999, then the range is "A2:H754999" (Rows 2 through 754,999)
  9. 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

Upvotes: 4

Related Questions