Suppression de lignes grand tableau, VBA

Résolu/Fermé
amiral2 Messages postés 19 Date d'inscription mercredi 15 mai 2013 Statut Membre Dernière intervention 12 juillet 2013 - 15 mai 2013 à 22:26
amiral2 Messages postés 19 Date d'inscription mercredi 15 mai 2013 Statut Membre Dernière intervention 12 juillet 2013 - 16 mai 2013 à 22:04
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

Patrice33740 Messages postés 8553 Date d'inscription dimanche 13 juin 2010 Statut Membre Dernière intervention 24 octobre 2022 1 758
15 mai 2013 à 22:54
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 

0
amiral2 Messages postés 19 Date d'inscription mercredi 15 mai 2013 Statut Membre Dernière intervention 12 juillet 2013
15 mai 2013 à 23:20
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 ?
0
Patrice33740 Messages postés 8553 Date d'inscription dimanche 13 juin 2010 Statut Membre Dernière intervention 24 octobre 2022 1 758
15 mai 2013 à 23:53
Oui, remplace
Dim i As Integer
par
Dim i As Long

0
amiral2 Messages postés 19 Date d'inscription mercredi 15 mai 2013 Statut Membre Dernière intervention 12 juillet 2013
16 mai 2013 à 09:04
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.
0
michel_m Messages postés 16582 Date d'inscription lundi 12 septembre 2005 Statut Contributeur Dernière intervention 27 novembre 2022 3 284
16 mai 2013 à 08:08
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
0
amiral2 Messages postés 19 Date d'inscription mercredi 15 mai 2013 Statut Membre Dernière intervention 12 juillet 2013
16 mai 2013 à 09:01
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.
0
michel_m Messages postés 16582 Date d'inscription lundi 12 septembre 2005 Statut Contributeur Dernière intervention 27 novembre 2022 3 284
16 mai 2013 à 10:12
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
0
amiral2 Messages postés 19 Date d'inscription mercredi 15 mai 2013 Statut Membre Dernière intervention 12 juillet 2013
16 mai 2013 à 10:36
Bonjour Michel_m,
Toutes les cellules de mon tableau contiennent uniquement des nombres, il n'y a nulle part du texte.
J'ai mis un extrait de mon tableau sur le lien suivant : https://www.cjoint.com/?3EqkDJQcDTj
Je vous remercie de votre aide.
0
michel_m Messages postés 16582 Date d'inscription lundi 12 septembre 2005 Statut Contributeur Dernière intervention 27 novembre 2022 3 284
16 mai 2013 à 10:42
Cjoint me signale que ton lien n'est pas disponible (il y a des limites de taille des fichiers) au besoin 100 lignes suffiraient

je t'envoie ma maquette de travail
https://www.cjoint.com/?3EqkPdzfghK
0
amiral2 Messages postés 19 Date d'inscription mercredi 15 mai 2013 Statut Membre Dernière intervention 12 juillet 2013
16 mai 2013 à 11:11
Rebonjour Michel_m,
J'ai regardé votre maquette mais j'ai constaté que les nombres de votre 2è colonne sont tous consécutifs alors que ce n'est pas le cas dans mon tableau. De plus, dans les autres champs, il n'y a que des nombres et jamais de texte. Peut-être est-ce une des raisons.
Voici un autre extrait de mon tableau (180 lignes) : https://www.cjoint.com/?3Eqli3z8miK
0
michel_m Messages postés 16582 Date d'inscription lundi 12 septembre 2005 Statut Contributeur Dernière intervention 27 novembre 2022 3 284
Modifié par michel_m le 16/05/2013 à 11:45
J'ai recopié ton tableau sur ma maquette et testé pas de problèmes

a noter que les colonnes ou tu as des "nombres" avec un point pour la virgule sont en fait des textes mais sont transformés en nombres par la macro
les nombres consécutifs ou pas n'ont rien à voir: c'était plus facile pour moi pour construire la maquette

j'ai écris un '316 dans la colonne 2 et j'ai l'erreur 13 au passage

j'ai donc changé la macro ainsi

For Cptr_in = 1 To UBound(T_in)
          If CLng(T_in(Cptr_in, 2)) Mod 10 = 0 Then
               Cptr_out = Cptr_out + 1
               ReDim Preserve T_out(Dercol, Cptr_out)


et ça a marché
donc, fais la modif et tu dis...
0
amiral2 Messages postés 19 Date d'inscription mercredi 15 mai 2013 Statut Membre Dernière intervention 12 juillet 2013
16 mai 2013 à 12:07
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.
0