responde - excel se traba con muchos datos

La macro de VBA se bloquea después de 32000 filas

Question

Tengo una macro de VBA que copia filas de una hoja de trabajo a otra en función de la búsqueda de valores en celdas en 3 columnas. La macro funciona, pero se bloquea cuando llega a la fila 32767. No hay fórmulas o formatos especiales en esta fila. Además, he eliminado esa fila, pero aún se bloquea en ese número de fila. ¿Es esta una limitación en excel? Hay unos 43000 en la hoja de trabajo que se está procesando.

Por lo tanto, pregunto qué es lo que está mal con mi macro y cómo puedo llegar al final de la hoja de trabajo:

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

Para cada semana en hojas de trabajo

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

¡Por favor ayuda!

Answer 1

El tipo VBA ''Int'' es un campo firmado de 16 bits, por lo que solo puede contener valores de -32768 a +32767. Cambie esas variables a ''Largo'', que es un campo con signo de 32 bits y puede contener valores de -2147483648 a [phone]. Debería ser suficiente para Excel. ;)

Answer 2

Esto suena como un problema entero

Los tipos de datos Integer y Long pueden contener valores positivos o negativos. La diferencia entre ellos es su tamaño: las variables enteras pueden contener valores entre -32,768 y 32,767 , mientras que las variables largas pueden variar entre -2,147,483,648 y 2,147,483,647.

Pero, ¿qué versión estás usando? Porque:

Tradicionalmente, los programadores de VBA han usado enteros para mantener números pequeños, porque requerían menos memoria. Sin embargo, en versiones recientes, VBA convierte todos los valores enteros al tipo Long, incluso si se declaran como tipo Integer. Por lo tanto, ya no hay una ventaja de rendimiento al usar variables enteras; de hecho, las variables largas pueden ser un poco más rápidas porque VBA no tiene que convertirlas.

Esta información es directamente de MSDN

ACTUALIZAR

Por favor, lea también el primer comentario! ¡Estaba interpretando la información de MSDN de manera incorrecta!

Eso es engañoso para MSDN: VBA no convierte el entero en largo. Bajo las coberturas, la CPU convierte el número entero en largo, hace la aritmética y luego convierte el resultado largo en entero. Así que los enteros de VBA aún no pueden contener números mayores a 32K - Charles Williams

Answer 3

Puede evitar el problema de Integer vs. Long usando un For Each en lugar de incrementar filas. Para cada uno es generalmente más rápido, ya que se evita la selección de rangos. Aquí hay un ejemplo:

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