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