VBA 宏在 32000 行后崩溃 [英] VBA Macro crashes after 32000 rows

问题描述

我有一个 VBA 宏，它根据在 3 列单元格中查找值将行从一个工作表复制到另一个工作表.该宏有效，但在到达第 32767 行时崩溃.该行中没有公式或特殊格式.此外，我已经删除了该行，但它仍然在该行号上崩溃.这是excel的限制吗?正在处理的工作表中有大约 43000 个

I have a VBA macro that copies rows from one worksheet into another based upon finding values in cells in 3 columns. The macro works, but crashes when it reaches row 32767. There are no formulas or special formatting in this row. Further, I have taken that row out, but it still crashes on that row number. Is this a limitation in excel? There are some 43000 in the worksheet that is being process

因此，我问我的宏有什么问题以及如何让它到达工作表的末尾:

Therefore, I ask what is wrong with my macro and how I can get it reach the end of the worksheet:

Dim LSearchRow As Integer
Dim LCopyToRow As Integer
Dim wks As Worksheet
On Error GoTo Err_Execute

对于工作表中的每个周

LSearchRow = 4
LCopyToRow = 4

ThisWorkbook.Worksheets.Add After:=Worksheets(Worksheets.Count)
Set wksCopyTo = ActiveSheet
wks.Rows(3).EntireRow.Copy wksCopyTo.Rows(3)

While Len(wks.Range("A" & CStr(LSearchRow)).Value) > 0

    If wks.Range("AB" & CStr(LSearchRow)).Value = "Yes" And wks.Range("AK" & CStr(LSearchRow)).Value = "Yes" And wks.Range("BB" & CStr(LSearchRow)).Value = "Y" Then

        Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
        Selection.Copy


        wksCopyTo.Select
        wksCopyTo.Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
        wksCopyTo.Paste

        'Move counter to next row
        LCopyToRow = LCopyToRow + 1
        'Go back to Sheet1 to continue searching
        wks.Select
    End If
    LSearchRow = LSearchRow + 1
Wend

Application.CutCopyMode = False
Range("A3").Select
MsgBox "All matching data has been copied."
Next wks
    Exit Sub
Err_Execute:
    MsgBox "An error occurred."

请帮忙！

推荐答案

VBA 'Int' 类型是一个有符号的 16 位字段，因此它只能保存从 -32768 到 +32767 的值.将这些变量更改为Long"，这是一个有符号的 32 位字段，可以保存从 -2147483648 到 [phone] 的值.Excel 应该够了.;)

The VBA 'Int' type is a signed 16-bit field so it can only hold values from -32768 to +32767. Change those variables to 'Long', which is a signed 32-bit field and can hold values from -2147483648 to [phone]. Should be enough for Excel. ;)

这篇关于VBA 宏在 32000 行后崩溃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！