CODEWITHSUNDEEP
vbaexcel
Andy5
Andy5

Reputation: 2405

VBA Macro crashes after 32000 rows

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 processed.

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
   
 
For Each wks In Worksheets

    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."

Upvotes: 17

Views: 49239

Answers (3)

Dick Kusleika
Dick Kusleika

Reputation: 33145

You can avoid the Integer vs. Long issue by using a For Each rather than incrementing rows. For Each is generally faster, as is avoiding Selecting Ranges. Here's an example:

Sub CopySheets()

    Dim shSource As Worksheet
    Dim shDest As Worksheet
    Dim rCell As Range
    Dim aSheets() As Worksheet
    Dim lShtCnt As Long
    Dim i As Long

    Const sDESTPREFIX As String = "dest_"

    On Error GoTo Err_Execute

    For Each shSource In ThisWorkbook.Worksheets
        lShtCnt = lShtCnt + 1
        ReDim Preserve aSheets(1 To lShtCnt)
        Set aSheets(lShtCnt) = shSource
    Next shSource

    For i = LBound(aSheets) To UBound(aSheets)
        Set shSource = aSheets(i)

        'Add a new sheet
        With ThisWorkbook
            Set shDest = .Worksheets.Add(, .Worksheets(.Worksheets.Count))
            shDest.Name = sDESTPREFIX & shSource.Name
        End With

        'copy header row
        shSource.Rows(3).Copy shDest.Rows(3)

        'loop through the cells in column a
        For Each rCell In shSource.Range("A4", shSource.Cells(shSource.Rows.Count, 1).End(xlUp)).Cells
            If Not IsEmpty(rCell.Value) And _
                rCell.Offset(0, 27).Value = "Yes" And _
                rCell.Offset(0, 36).Value = "Yes" And _
                rCell.Offset(0, 53).Value = "Yes" Then

                'copy the row
                rCell.EntireRow.Copy shDest.Range(rCell.Address).EntireRow
            End If
        Next rCell
    Next i

    MsgBox "All matching data has been copied."

Err_Exit:
    'do this stuff even if an error occurs
    On Error Resume Next
    Application.CutCopyMode = False
    Exit Sub

Err_Execute:
    MsgBox "An error occurred."
    Resume Err_Exit

End Sub

Upvotes: 2

Eight-Bit Guru
Eight-Bit Guru

Reputation: 9971

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 +2147483647. Should be enough for Excel. ;)

Upvotes: 38

Neysor
Neysor

Reputation: 3909

This sounds like an integer Problem

The Integer and Long data types can both hold positive or negative values. The difference between them is their size: Integer variables can hold values between -32,768 and 32,767, while Long variables can range from -2,147,483,648 to 2,147,483,647.

But which version are you using? Because:

Traditionally, VBA programmers have used integers to hold small numbers, because they required less memory. In recent versions, however, VBA converts all integer values to type Long, even if they are declared as type Integer. Therefore, there is no longer a performance advantage to using Integer variables; in fact, Long variables might be slightly faster because VBA does not have to convert them.

This Information is directly from MSDN

UPDATE

Please also read the first comment! I was interpreting the MSDN Information the wrong way!

Thats MSDN being misleading: VBA does not itself convert Integer to Long. Under the covers the CPU converts integer to long , does the arithmetic and then converts the resulting long back to integer. So VBA integers still cannot hold numbers larger than 32K – Charles Williams

Upvotes: 7

Related Questions