Cherche astuce pour accélérer une boucle

Question

Bonjour à tous,

J'ai dans une macro une boucle qui doit lire un tableau de plus de 400 000 lignes et supprimer des lignes quand une valeur est trouvée.

Dans un onglet "sku_to_ban" je liste les X valeurs dont il faut supprimer les lignes (18 valeurs à ce jour, en occurence unique dans la grande liste)
Dans l'autre "report_SAP" la liste à nettoyer (plus de 400 000 lignes).

Me doutant qu'un boucle For i multipliée X fois serait très longue j'ai opté pour un code comme suit :

Dès qu'une valeur est trouvée, la ligne est supprimée et on passe à la valeur suivante cherchée sans aller jusqu'à la fin de la liste report_SAP.

Des idées pour améliorer le code s'il vous plait ?

Merci

Sheets("sku_to_ban").Select

   N_lg_sku = Cells(Rows.Count, 1).End(xlUp).Row

For j = 1 To N_lg_sku
    Sheets("sku_to_ban").Select
    sup_sku = Range("A" & j).Value
    Sheets("report_SAP").Select

    Set c = Cells.Find(sup_sku, , , xlWhole)
  If Not c Is Nothing Then
    x = c.Row
End If
Rows(x).Delete

Next j

galopin01 · Accepted Answer

Quelque chose comme ça devrait convenir: Sub Galopin() Dim Arr, iLR&, c, j With Sheets("sku_to_ban") iLR = .Cells(Rows.Count, 1).End(xlUp).Row Arr = .Range("A1:A" & iLR).Value End With With Sheets("report_SAP") For j = 1 To iLR Set c = .Cells.Find(Arr(j, 1), , , xlWhole) If Not c Is Nothing Then .Rows(c.Row).Delete End If Next End With End Sub A+...

eriiic · Accepted Answer

Il y a un pb dans ta macro. Les suppressions de lignes doivent se faire depuis la fin, en remontant. Sinon quand tu en supprimes une, tu remontes les autres et la ligne n+1 n'est pas traitée vu que ton incrément de boucle se fait Tu peux aussi faire une union de plages dans une variable Range quand tu en trouves une, et les supprimer d'un coup à la fin. Il vaut mieux le faire par petits paquets (100 à 200) En ligne à ligne il faut ajouter Application.ScreenUpdating=False au début...

eriiic · Accepted Answer

J'avais lu un peu trop en diagonale, là tu peux boucler normalement.
Je regarderai un peu plus tard pour te fournir un exemple.
En attendant tu devrais fournir un fichier de travail (anonymisé) de qq lignes pour tester et éviter les erreurs d'interprétation.

eriiic · Accepted Answer

Tu pouvais te contenter de 100 lignes...

Je suis passé de 2.58 s à 0.07 s sur ton fichier.
Du coup je n'ai pas comparé avec un .find()
Par curiosité dis le temps pour 400k lignes

Contrôle si ça te parait ok

Option Explicit

Sub nettoyage_sku()
    'nettoyage des sku to ban
    Dim t_sku, datas, pl As Range
    Dim lig As Long, lig2 As Long, nb As Long
    Dim t As Single

    t = Timer
    Application.ScreenUpdating = False
    With Sheets("sku_to_ban")
        lig = .Cells(Rows.Count, 1).End(xlUp).Row
        t_sku = .Range("A1:A" & lig).Value
    End With

    With Sheets("report_SAP")
        lig = .Cells(Rows.Count, 1).End(xlUp).Row
        datas = .Range("A2:A" & lig).Value
        For lig = 1 To UBound(t_sku)
            For lig2 = 1 To UBound(datas)
                If t_sku(lig, 1) = datas(lig2, 1) Then
                    If pl Is Nothing Then Set pl = .Rows(lig2 + 1).EntireRow Else Set pl = Union(pl, .Rows(lig2 + 1).EntireRow)
                    nb = nb + 1
                    Exit For
                End If
            Next lig2
        Next lig
        If Not pl Is Nothing Then pl.Delete
    End With
    MsgBox nb & "/" & UBound(t_sku) & " sku supprimés en " & Format(Timer - t, "0.000") & " s."
End Sub

Déclarer ses variables c'est bien, mais il faut les typer au besoin le plus proche.
Les laisser Variant ralenti le code.

Et coche ça dans les options de VBE

la déclaration obligatoire des variables te permet de les typer au passage, mais surtout d'affranchit des erreurs de saisie sur leur nom qui font des bug parfois difficiles à détecter et à rechercher.

eriiic · Accepted Answer

PS : évite les.Select inutiles dans 99% des cas. Ca, ça ralenti considérablement J'ai fait qq petites retouches, assure toi d'avoir le dernier code...

BsAlv · Answer

Sub M_Kerdaven() t = Timer With Sheets("sku_to_ban") .Range("A1:A" & .Range("A" & Rows.Count).End(xlUp).Row).Name = "kerdaven" End With With Sheets("Report_SAP") On Error Resume Next .AutoFilter.Range.AutoFilter On Error GoTo 0 With .Range("A1:A" & .Range("A" & Rows.Count).End(xlUp).Row).Offset(, 26) 'colonne auxiliaire : AA de Report_Sap .FormulaR1C1 = "=n(isnumber(MATCH(RC1,sku_to_ban!R1C1:R10C1,0)))" '--&gt;les sku qui sont dans sku_to_ban = 1, let autres = 0 .Cells(1).Value = "aaa" .AutoFilter 1, 1 'filtrer les sku connu dans sku_to_ban If .SpecialCells(xlVisible).Areas.Count &gt; 8000 Then MsgBox "problem": Exit Sub 'bug d'excel à partir de 2^13 .Offset(1).SpecialCells(xlVisible).EntireRow.Delete 'supprimer les lignes visibles .AutoFilter 'supprimer le filtre .EntireColumn.Clear 'vider colonne auxiliaire End With End With MsgBox "temps pour boire une tasse de café : " & Format(Timer - t, "0.00\s") End Sub...

BsAlv · Answer

@Eriiic, il y a un problème avec union, dès que le nombre de cellules dans cet union est trop grand, cela ralentit les choses. Je ne suis pas sûr mais dès que cela dépasse les 100 cellules, il faut vider cet union et continuer...

eriiic · Answer

Oui, je lui en ai parlé et il annonce une vingtaine de valeurs, donc pas de traitement en plus ici.
Sinon, il faut faire par petits blocs en effet.
D'ailleurs je me pose encore la question si ça dépend vraiment du nombre de cellules ou du nombre d'Areas ou encore d'Unions réalisées
La flemme de tester...

BsAlv · Answer

Oei, pas aujourd'hui, ma tête ......

eriiic · Answer

La méthode des filtres est très optimisée sur excel. Tu devrais tester la proposition de BsAlv. Sur 400k lignes il est probable que tu aies un gain significatif eric...