0

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

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Dinh Truong Anh Phuong
  • 127
  • 10
  • 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 Aug 21 '18 at 13:45
  • 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 Aug 21 '18 at 13:45
  • Try using [this link](https://stackoverflow.com/questions/6901991/how-to-return-the-number-of-dimensions-of-a-variant-variable-passed-to-it-in-v), which provides a few methods of determining array dimensions – urdearboy Aug 21 '18 at 13:46
  • You can try `ThisWorkbook.Sheets("Sheet1").Range("F2").Resize(lsrw - 1, 18) = rearr` – urdearboy Aug 21 '18 at 13:57
  • I tried but It still has the error. i and lsrw = 930507, but ubound (arr()) = 930506. Is it OK?. – Dinh Truong Anh Phuong Aug 21 '18 at 14:07
  • Not sure. Maybe it would help if you give some context to your code. What is the goal of the macro – urdearboy Aug 21 '18 at 14:51
  • I change the last row = 50000, the code is OK. I don't think it is about the content. I think i resize wrong. The error appeared when i changed last row over 100k – Dinh Truong Anh Phuong Aug 21 '18 at 14:59
  • What version of Excel is this? – RBarryYoung Aug 21 '18 at 15:47

1 Answers1

0

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.

RBarryYoung
  • 55,398
  • 14
  • 96
  • 137
  • Thanks. I hope my file doesnt be limit 65k rows. I try to seperate but now the code just run 30-50k rows – Dinh Truong Anh Phuong Aug 21 '18 at 16:12
  • 1
    I think the issue is about the data in cells not about the row. I just removed some content in cells, and limit the range. The error didnt appear, still 900k rows. I think there are too many information, it has error. Thanks – Dinh Truong Anh Phuong Aug 22 '18 at 04:14