Jump to content
eugeniocol

ANSWERED Problema con limite filas y columnas

Recommended Posts

Hola buenas.

Tengo una macro para combinacion de numeros, pero dado el limite en excel
Número total de filas y columnas en una hoja de cálculo 1.048.576 filas por 16.384 columnas

No puede finalizar la macro se queda pillada en la fila 810.00, la idea es que se ejecute ejemplo hasta la fila 500.00 y siga en otras columnas o rango ejemplo k:m

Esta es la macro:

Sub COMBINAR_4()

Columns(5).ClearContents
Columns(6).ClearContents
Columns(7).ClearContents
Columns(8).ClearContents
Columns(9).ClearContents


FIN_A = Range("A1", Range("A" & Rows.Count).End(xlUp)).Rows.Count
FIN_B = Range("B1", Range("B" & Rows.Count).End(xlUp)).Rows.Count
FIN_C = Range("C1", Range("C" & Rows.Count).End(xlUp)).Rows.Count
FIN_D = Range("D1", Range("D" & Rows.Count).End(xlUp)).Rows.Count

For X = 1 To FIN_A
   For Y = 1 To FIN_B
    For Z = 1 To FIN_C
     For AA = 1 To FIN_D
      FIN_E = FIN_E + 1
      
      
      Cells(FIN_E, 6) = Cells(X, 1)
      Cells(FIN_E, 7) = Cells(Y, 2)
      Cells(FIN_E, 8) = Cells(Z, 3)
      Cells(FIN_E, 9) = Cells(AA, 4)
     Next AA
    Next Z
   Next Y
Next X

End Sub

 

Un Saludo.

 

Share this post


Link to post
Share on other sites

Hola @eugeniocol, una solución simple y sin alterar el código que ya tenias es esta:

Sub COMBINAR_4()

Columns(5).ClearContents
Columns(6).ClearContents
Columns(7).ClearContents
Columns(8).ClearContents
Columns(9).ClearContents


FIN_A = Range("A1", Range("A" & Rows.Count).End(xlUp)).Rows.Count
FIN_B = Range("B1", Range("B" & Rows.Count).End(xlUp)).Rows.Count
FIN_C = Range("C1", Range("C" & Rows.Count).End(xlUp)).Rows.Count
FIN_D = Range("D1", Range("D" & Rows.Count).End(xlUp)).Rows.Count

For x = 1 To FIN_A
   For Y = 1 To FIN_B
    For Z = 1 To FIN_C
     For AA = 1 To FIN_D
      FIN_E = FIN_E + 1
      On Error GoTo c
c:       If Err.Number = 1004 Then t = t + 5: FIN_E = 1: On Error GoTo -1
      Cells(FIN_E, 6 + t) = Cells(x, 1)
      Cells(FIN_E, 7 + t) = Cells(Y, 2)
      Cells(FIN_E, 8 + t) = Cells(Z, 3)
      Cells(FIN_E, 9 + t) = Cells(AA, 4)
     Next AA
    Next Z
   Next Y
Next x

End Sub

Saludos.

Share this post


Link to post
Share on other sites
Hace 15 horas, AlexanderS dijo:

Hola @eugeniocol, una solución simple y sin alterar el código que ya tenias es esta:


Sub COMBINAR_4()

Columns(5).ClearContents
Columns(6).ClearContents
Columns(7).ClearContents
Columns(8).ClearContents
Columns(9).ClearContents


FIN_A = Range("A1", Range("A" & Rows.Count).End(xlUp)).Rows.Count
FIN_B = Range("B1", Range("B" & Rows.Count).End(xlUp)).Rows.Count
FIN_C = Range("C1", Range("C" & Rows.Count).End(xlUp)).Rows.Count
FIN_D = Range("D1", Range("D" & Rows.Count).End(xlUp)).Rows.Count

For x = 1 To FIN_A
   For Y = 1 To FIN_B
    For Z = 1 To FIN_C
     For AA = 1 To FIN_D
      FIN_E = FIN_E + 1
      On Error GoTo c
c:       If Err.Number = 1004 Then t = t + 5: FIN_E = 1: On Error GoTo -1
      Cells(FIN_E, 6 + t) = Cells(x, 1)
      Cells(FIN_E, 7 + t) = Cells(Y, 2)
      Cells(FIN_E, 8 + t) = Cells(Z, 3)
      Cells(FIN_E, 9 + t) = Cells(AA, 4)
     Next AA
    Next Z
   Next Y
Next x

End Sub

Saludos.

En principio funcionan ok l

Pero el libro se hace muy pesado, la solución que no pensé ni comente es que los datos repetidos más de una  vez  en  fila por ejemplo 1 1 2 3  o sea el 1 me sobra la fila entera.

Esta macro (no mia) lo hace y elimina un montón de filas.

Sub COMBINAR_4()

For H = 5 To 35
Columns(H).ClearContents
Next H
M = 6
N = 7
O = 8
P = 9
FIN_A = Range("A1", Range("A" & Rows.Count).End(xlUp)).Rows.Count
FIN_B = Range("B1", Range("B" & Rows.Count).End(xlUp)).Rows.Count
FIN_C = Range("C1", Range("C" & Rows.Count).End(xlUp)).Rows.Count
FIN_D = Range("D1", Range("D" & Rows.Count).End(xlUp)).Rows.Count

For x = 1 To FIN_A
   For Y = 1 To FIN_B
    For Z = 1 To FIN_C
      For AA = 1 To FIN_D
        If FIN_E > 1000000 Then
            FIN_E = 0
            M = M + 5
            N = N + 5
            O = O + 5
            P = P + 5
        End If
        If Cells(x, 1).Value <> Cells(Y, 2).Value Then
        If Cells(x, 1).Value <> Cells(Z, 3).Value Then
        If Cells(x, 1).Value <> Cells(AA, 4).Value Then
            If Cells(Y, 2).Value <> Cells(Z, 3).Value Then
            If Cells(Y, 2).Value <> Cells(AA, 4).Value Then
                If Cells(Z, 3).Value <> Cells(AA, 4).Value Then
                    FIN_E = FIN_E + 1
                    Cells(FIN_E, M) = Cells(x, 1)
                    Cells(FIN_E, N) = Cells(Y, 2)
                    Cells(FIN_E, O) = Cells(Z, 3)
                    Cells(FIN_E, P) = Cells(AA, 4)
                End If
            End If
            End If
        End If
        End If
        End If
      Next AA
    Next Z
   Next Y
Next x
End Sub

 

Share this post


Link to post
Share on other sites

×
×
  • Create New...

Important Information

Privacy Policy