Suppression de lignes grand tableau, VBA

[Résolu/Fermé]
Signaler
Messages postés
19
Date d'inscription
mercredi 15 mai 2013
Statut
Membre
Dernière intervention
12 juillet 2013
-
Messages postés
19
Date d'inscription
mercredi 15 mai 2013
Statut
Membre
Dernière intervention
12 juillet 2013
-
Bonsoir à tous,

Je dispose d'un tableau de plus de 1 million de lignes et d'une vingtaine de colonnes sur Excel 2010. Mon but est de supprimer toutes les lignes de ce tableau dont les valeurs de la deuxième colonne ne sont pas divisibles par 10.

Pour cela, j'ai écrit un code VBA mais étant débutant, ce dernier ne marche pas pour plus de 10000 lignes. J'ai laissé tourner mon ordinateur plus de 1h pour le tableau et il a planté...

Voici mon code VBA :

Sub supprimer()

Dim i As Integer
Application.ScreenUpdating = False

With Sheets("Feuil1")

For i = 1 To 1048000
    If (Cells(i, 2) Mod 10 <> 0) Then
    Cells(i, 1).EntireRow.Delete
    End If
Next i

End With

Application.ScreenUpdating = True

End Sub


Pourriez-vous m'aider, membres de cette merveilleuse communauté "Comment ça marche"?

4 réponses

Messages postés
8539
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
20 juillet 2021
1 699
Essaies ce code :

Sub supprimer()

Dim i As Integer
Application.ScreenUpdating = False

With Sheets("Feuil1")

For i = 1048000 To 1 Step -1
    If (.Cells(i, 2) Mod 10) <> 0 Then
    .Rows(i).Delete
    End If
Next i

End With

Application.ScreenUpdating = True

End Sub 

Messages postés
19
Date d'inscription
mercredi 15 mai 2013
Statut
Membre
Dernière intervention
12 juillet 2013

Ce code ne marche pas non plus Patrice33740. Excel me signale : "Erreur d'exécution '6' Dépassement de capacité"
Auriez-vous d'autres idées ?
Messages postés
8539
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
20 juillet 2021
1 699
Oui, remplace
Dim i As Integer
par
Dim i As Long

Messages postés
19
Date d'inscription
mercredi 15 mai 2013
Statut
Membre
Dernière intervention
12 juillet 2013

Bonjour Patrice33740,
J'ai remplacé Integer par Long pour la variable i mais cela ne fonctionne toujours pas pour le tableau entier. Je n'arrive pas à dépasser la dizaine de milliers de lignes.
Pensez-vous à autre chose ? Michel_m m'a suggéré un code en utilisant des variables tableaux mais cela génère une erreur chez moi.
Messages postés
16523
Date d'inscription
lundi 12 septembre 2005
Statut
Contributeur
Dernière intervention
28 septembre 2021
3 229
Bonjour

Vu la taille du tableau, Access 'ou une autre database aurait du être choisi
voir à ce sujet
https://support.microsoft.com/fr-fr/office/utiliser-access-ou-excel-pour-g%c3%a9rer-vos-donn%c3%a9es-09576147-47d1-4c6f-9312-e825227fcaea?ocmsassetid=ha010210195&correlationid=d29af1f8-4e74-4502-b994-3b1f030cc1ff&ui=fr-fr&rs=fr-fr&ad=fr#BM2

pour ne passer de longues minutes ou heures, il vaut peut-^tre mieux passer par des variables- tableaux (on ne travaille plus en Excel mais en RAM)

proposition avec restitution en feuille2 pendant les essais pour ne pas détruire le tableau à traiter
Option Explicit
Option Base 1

Sub supprimer_si_non10()
Dim Derlig As Long, Dercol As Byte, T_in()
Dim Cptr_in As Long, T_out(), Cptr_out As Long, Col As Byte

Dim start As Single ' A SUPPRIMER APRES ESSAIS
start = Timer ' A SUPPRIMER APRES ESSAIS

'Application.ScreenUpdating = False
With Sheets(1)
     Derlig = .Columns("B").Find("*", , , , , xlPrevious).Row
     Dercol = .Rows(1).Find("*", , , , , xlPrevious).Column
     T_in = .Range(.Cells(2, "A"), .Cells(Derlig, Dercol)).Value
     ReDim T_out(Dercol, 1)
     
     For Cptr_in = 1 To UBound(T_in)
          If T_in(Cptr_in, 2) Mod 10 = 0 Then
               Cptr_out = Cptr_out + 1
               ReDim Preserve T_out(Dercol, Cptr_out)
               For Col = 1 To Dercol
                    T_out(Col, Cptr_out) = T_in(Cptr_in, Col)
               Next Col
          End If
     Next Cptr_in
     '.Range(.Cells(2, "A"), .Cells(Derlig, Dercol)).ClearContents ' A ACTIVER APRES ESSAI
     '.Range("A2").Resize(Cptr_out, Dercol) = Application.Transpose(T_out) ' A ACTIVER APRES ESSAI
End With

With Sheets(2) ' A SUPPRIMER APRES ESSAIS
     .Range("A2").Resize(Cptr_out, Dercol) = Application.Transpose(T_out) ' A SUPPRIMER APRES ESSAIS
     .Select ' A SUPPRIMER APRES ESSAIS
End With ' A SUPPRIMER APRES ESSAIS

MsgBox "traitement réussi en : " & Timer - start & " .secondes" ' A SUPPRIMER APRES ESSAIS
End Sub

Pour 5000 lignes et 20 colonnes durée 1/10 secondes (Ram 512 Mo et proc 3 Go
Messages postés
19
Date d'inscription
mercredi 15 mai 2013
Statut
Membre
Dernière intervention
12 juillet 2013

Bonjour michel_m,
J'ai essayé votre code mais après quelques minutes, Excel m'indique "Erreur d'exécution '13' Incompatibilité de type". Comprenez-vous pourquoi ? Etant débutant, je n'ai pas tout compris à votre code donc je ne vois pas d'où cela peut provenir.
Messages postés
16523
Date d'inscription
lundi 12 septembre 2005
Statut
Contributeur
Dernière intervention
28 septembre 2021
3 229
chez moi pas de problème

comme on peut penser que ton tableau provient d'ailleurs (base de données ou fichier texte), tu as peut-^tre des nombres écrits en texte dans ta colonne B;
si, sans rien changer à ton tableau, les données dans la 2° colonne sont à gauche ==> texte au lieu de nombres
mais vu la taille de ton tableau 20 millions de cellules, l'ordi se met peut-^tre à genoux

au besoin

met quelques lignes de ton tableau (1000 maxi) sans données confidentielles en pièce jointe sur
https://www.cjoint.com/
puis copier l'adresse du lien et la coller dans le message de réponse

de mon coté je reconstruis la maquette et éventuellement te la transmets

dans cette attente
Messages postés
19
Date d'inscription
mercredi 15 mai 2013
Statut
Membre
Dernière intervention
12 juillet 2013

J'ai fait la modif et exécuté le code mais au bout de 5 mn d'exécution, Excel m'indique "Erreur d'exécution '13' Incompatibilité de type". C'est vraiment bizarre...
Je vous souhaite par ailleurs un bon appétit.
Messages postés
16523
Date d'inscription
lundi 12 septembre 2005
Statut
Contributeur
Dernière intervention
28 septembre 2021
3 229
Merci, bon appétit aussi à toi

mais là, je ne vois plus...
Messages postés
19
Date d'inscription
mercredi 15 mai 2013
Statut
Membre
Dernière intervention
12 juillet 2013

Bonjour Michel_m,
J'ai finalement réussi après avoir fractionné mon tableau.
Je vous remercie infiniment pour votre aide qui m'a été très précieuse !!

PS : Si cela ne vous dérange pas, pourriez-vous m'expliquer les grandes lignes du code final et pourquoi cela ne marchait pas au début ?
Messages postés
16523
Date d'inscription
lundi 12 septembre 2005
Statut
Contributeur
Dernière intervention
28 septembre 2021
3 229
pourquoi cela ne marchait pas au début ?

tableau trop grand: avec mon vieux coucou (ram 512 Mo) j'avais planté pour un pB similaire avec 600000 lignes et je ne sais plus combien de colonnes et ca tournait plusieurs minutes (tu viens de me rappeler ces grands moment de solitude)

Mais on a dépassé les capacités d'Excel: comme tu es étudiant, fais attention lors du jury informatique, prépare une parade :o)

quelle est la durée de traitement; j'estimais à 20 secondes

Pour les explications regarde celles ci sur ce lien: ce n'est pas le m^me pb mais le principe est similaire
https://forums.commentcamarche.net/forum/affich-27799503-optimisation-programme-vba#11

et aussi tuto sur VBA
https://bidou.developpez.com/article/VBA/
Messages postés
19
Date d'inscription
mercredi 15 mai 2013
Statut
Membre
Dernière intervention
12 juillet 2013

Merci beaucoup pour votre aide et vos explications ! Elles m'ont été d'une grande utilité !