run time error 1004 object application defined or object defined error

Question

my code has error "Runtime error 1004 Application defined or object define error". My excel file doesn't have protect sheets or cells. But the code still appears the error in the last line Please help me. The file contains more 900k rows, and 25 columns.

option explicit
    Sub remove()
Dim i, j As Long
Dim a As Variant
Dim lsrw As Long
Dim arr()
Dim rearr()
lsrw = Sheet1.Range("A1000000").End(xlUp).Row

arr = Sheet1.Range("F2:W" & lsrw).Value

ReDim rearr(1 To UBound(arr), 1 To 18)

        For i = 1 To UBound(arr())
            For j = 1 To 18
                If arr(i, j) <> "" Then
                    If Len(arr(i, j)) > 9 Then
                        If InStr(1, Trim(arr(i, j)), " ") > 0 Then
                            For Each a In Split(arr(i, j), " ")
                                If Len(a) >= 9 Then
                                    rearr(i, j) = arr(i, j)
                                    Exit For
                                Else: rearr(i, j) = ""
                                End If
                            Next a
                        Else: rearr(i, j) = arr(i, j)
                        End If
                    Else: rearr(i, j) = ""
                    End If
                End If
            Next j
        Next i              
Sheets("Sheet1").Range("F2").Resize(i - 1, 18) = rearr ==>error

End Sub

If i add "on error resume next" i find out that the code could excute 95k line before break

Maybe your code is looking to a different book, qualify your sheet with ThisWorkbook.Sheets("Sheet1").Range("F2").Resize(i - 1, 18) = rearr. The other potential issue is that you are not resizing your range (F2) to match the dimensions of the array. — urdearboy
I made thisworkbook.sheets(sheet1") already.I think i make some mistake in resizing but i don't know how to fix. — Dinh Truong Anh Phuong
Try using this link, which provides a few methods of determining array dimensions — urdearboy
You can try ThisWorkbook.Sheets("Sheet1").Range("F2").Resize(lsrw - 1, 18) = rearr — urdearboy
I tried but It still has the error. i and lsrw = 930507, but ubound (arr()) = 930506. Is it OK?. — Dinh Truong Anh Phuong

RBarryYoung RBarryYoung · Accepted Answer · 2018-08-21T16:01:13

I have never seen official confirmation of this, but there are a number of reports of this problem, followed by claims that there is an Array-to-Range copy limit of 65536 rows in Excel 2010 and above (see here, for instance: https://social.msdn.microsoft.com/Forums/en-US/c45d37f1-a5f1-4cf4-938d-69d294d8e447/limitations-on-arrays?forum=isvvba)

Again, I cannot find anything official on this.

The only obvious workaround for this would be, instead of dumping the whole array to a range at once, to rather copy it out in chunks every 65,000 rows or so.

run time error 1004 object application defined or object defined error

1 Answers