XL 2016 Comment supprimer toutes les lignes contenant au moins un des mots

youns33 · 12 Décembre 2017

Bonjour,

Alors j'ai trouvé des post pour supprimer les lignes contenant un mot, mais mon souci est que j'aimerais supprimer d'un coup toutes les lignes contenant au moins un des mots d'une liste que l'on pourrait modifier ou allonger à souhait.

J'ai mis un fichier en PJ pour que se soit plus parlant.

En feuil1 j'ai mon tableaux avec les lignes à supprimer et en feuil2 colonne A j'ai ma liste (customisable) avec les suite de caractères à rechercher pour supprimer les lignes correspondantes en feuil1.

Donc si un expert à une petite macro qui irai bien je suis preneur.

Merci d'avance à tous

zebanx · 12 Décembre 2017

Bonsoir youns33

En reprenant un code du forum, un essai

++

mapomme · 13 Décembre 2017

Bonjour youns33, zebanx,

Pour le fun, deux autres pistes sans doute un peu plus rapides si les lignes de la sources sont nombreuses.

youns33 · 13 Décembre 2017

Bonjour à tous les 2 et merci pour vos réponses, j'ai environ 200000 lignes.

Par contre mapomme, j'ai l'impression que ton fichier ne supprime pas les ligne mais efface juste le contenu des cellules concernées !!
Et pourrait tu m'expliqué à quoi servent les différents réglages? Le pourcentage surtout?

Merci

mapomme · 13 Décembre 2017

Re,

youns33 à dit:
Par contre mapomme, j'ai l'impression que ton fichier ne supprime pas les ligne mais efface juste le contenu des cellules concernées !!

Peux tu me dire ce qui te fais dire cela (chez moi ça a l'air de fonctionner) ?

youns33 à dit:
Et pourrais tu m'expliquer à quoi servent les différents réglages? Le pourcentage surtout?

La procédure ReInit est juste là pour les tests ! Pour un jeu de test:

Nombre lignes Source est le nombre de ligne à initialiser sur Feuil1
Proportion à supprimer est le nombre de lignes (en%) à supprimer et à construire au moment de l'initialisation après l'initialisation.

Attention ! suivant le grand nombre de lignes, suivant la mémoire vive disponible, la méthode 2 peut éventuellement échouer (débordement de mémoire lors de lecture des tableaux))

job75 · 13 Décembre 2017

Bonsoir youn33, zebanx, mapomme,

Cette méthode de filtrage a l'avantage de conserver les formats, donc les liens hypertextes :

Code:

Sub Supprimer()
Dim t#
t = Timer
Application.ScreenUpdating = False
With [A1].CurrentRegion
  If .Parent.FilterMode Then .Parent.ShowAllData 'si la feuille est filtrée
  .Columns(5) = "=1/SUMPRODUCT(COUNTIF(A1,""*""&Liste&""*""))"
  .Columns(5) = .Columns(5).Value 'supprime les formules
  .Resize(, 5).Sort .Columns(5), xlDescending, Header:=xlYes 'tri pour accélérer
  On Error Resume Next 'si aucune SpecialCell
  .Columns(5).SpecialCells(xlCellTypeConstants, 1).EntireRow.Delete
  .Columns(5).ClearContents
  With .Parent.UsedRange: End With 'actualise la barre de défilement verticale
End With
Application.ScreenUpdating = True
MsgBox "Durée " & Format(Timer - t, "0.00 \s"), , "Suppression"
End Sub

Sur le fichier joint de 199 999 lignes la macro s'exécute chez moi en 63 secondes*.

*Edit : c'est le tri qui prend du temps, plus de 60 secondes, sans doute à cause des liens hypertextes.

Noter que la MFC n'augmente pas la durée de l'opération.

L'ouverture du fichier est un peu laborieuse...

A+

job75 · 13 Décembre 2017

Re,

Le tri prend du temps mais il est indispensable.

J'ai testé en le supprimant et suis sorti par le Gestionnaire des tâches au bout de 10 minutes.

A+

job75 · 13 Décembre 2017

Re,

La macro précédente faisait la recherche uniquement sur la colonne A en Feuil1.

Si on veut la faire sur les 4 colonnes il suffit de remplacer A1 par A1: D1 dans la formule VBA.

Et $A2 par $A2:$D2 dans la MFC.

Dans la liste j'ai ajouté "jean16" qui se trouve en colonne B.

La nouvelle macro s'exécute chez moi en 90 secondes.

Le tri s'effectue en 57 secondes mais l'entrée/calcul des formules prend maintenant 30 secondes.

Fichier (2).

A+

zebanx · 13 Décembre 2017

Bonsoir Job75, MaPomme, Youns33

Merci pour les codes (déjà) proposés.

@job75
En pas à pas détaillé, c'est la partie de tri, indispensable, qui semble prendre le plus de temps.

Sur une large feuille de travail, ne serait-il pas envisageable de prévoir :
e1- de découper cette feuille par bloc de 20000/30000 lignes
e2- de boucler ta macro sur toutes les feuilles (dans ton exemple entre 6 et 10 feuilles intermédiaires après découpage)
e3- de faire un récapitulatif sur une feuille finale des lignes restantes après macro
e4- de supprimer les feuilles intermédiaires

Pour avoir testé ton code (#6) sur 3 feuilles avec un découpage de 20000 lignes, le temps de la deuxième étape serait de moins de 5 secondes contre 12 secondes pour 65000 lignes sur mon poste.
Je ne sais pas si un découpage par bloc est long à coder, pour les étapes 3 et 4, c'est rapide et la macro devrait être rapide à s'exécuter sur ces parties là.
C'est peut-être intéressant au-delà d'une certaine limite. Si on reste dans une fourchette < 50000 lignes, ton code serait bien suffisant.

++ zebanx

job75 · 13 Décembre 2017

Re zebanx,

Tu as sans doute raison pour le découpage en feuilles mais là on se dirige vers l'usine à gaz.

Il faut bien voir que la macro ne sera pas lancée souvent et la durée du tri (une minute) est acceptable.

Par contre si la liste des mots à supprimer augmente le calcul des formules risque de prendre beaucoup de temps et là le découpage en feuilles n'apportera rien.

A+

zebanx · 13 Décembre 2017

Merci Job75 pour ta réponse.

Pour information, j'ai trouvé un code de saucissonage de fichier avec un nombre de lignes à choisir par inputbox.
J'arrive à 12 secondes pour 50000 lignes découpées par bloc de 4000 lignes suivant les étapes décrites en #9.
En l'imaginant travailler sur tes 200 000 lignes on serait au-dessus du temps de tes codes, mais ça m'a permis de charger un nouveau code, c'est pas si mal.

Bonne soirée
zebanx

https://stackoverflow.com/questions/17052240/excel-macro-to-create-new-sheet-every-n-rows

Staple1600 · 13 Décembre 2017

Bonsoir le fil, le forum

Je ne sais si mon idée est ici applicable: à savoir, prendre le problème à l'envers.
Copier dans une nouvelle feuille les lignes qui ne contiennent pas au moins un mot.
Est-ce qu'il est plus rapide de copier (de surcroît si on passe par un tableau) que de supprimer des lignes?

job75 · 13 Décembre 2017

Re,

Je m'en doutais, un SUMPRODUCT avec A1: D1 prend bien plus de temps que 4 SUMPRODUCT :

Code:

.Columns(5) = "=1/(SUMPRODUCT(COUNTIF(A1,""*""&Liste&""*""))+SUMPRODUCT(COUNTIF(B1,""*""&Liste&""*""))+SUMPRODUCT(COUNTIF(C1,""*""&Liste&""*""))+SUMPRODUCT(COUNTIF(D1,""*""&Liste&""*"")))"

La macro de ce fichier (3) s'exécute en 66 secondes (au lieu de 90 secondes précédemment).

A+

Staple1600 · 13 Décembre 2017

Re, Bonsoir Job75

J'en déduis que l'idée exprimée dans le message#12 n'est pas ici efficiente?
Ou plus simplement que mon message a été zappé?

job75 · 13 Décembre 2017

Re,

JM tu n'as qu'à faire ce que tu suggères et tu verras.

Quant aux tableaux VBA ils ne sont pas utilisables ici car les liens ne seraient pas conservés, c'est évident.

Comme je l'ai dit le tri dans mes macros serait très rapide sans les liens hypertextes.

A+

XL 2016 Comment supprimer toutes les lignes contenant au moins un des mots

youns33

XLDnaute Nouveau

Pièces jointes

zebanx

XLDnaute Accro

Pièces jointes

mapomme

XLDnaute Barbatruc

Pièces jointes

youns33

XLDnaute Nouveau

mapomme

XLDnaute Barbatruc

job75

XLDnaute Barbatruc

Pièces jointes

job75

XLDnaute Barbatruc

job75

XLDnaute Barbatruc

Pièces jointes

zebanx

XLDnaute Accro

job75

XLDnaute Barbatruc

zebanx

XLDnaute Accro

Pièces jointes

Staple1600

XLDnaute Barbatruc

job75

XLDnaute Barbatruc

Pièces jointes

Staple1600

XLDnaute Barbatruc

job75

XLDnaute Barbatruc

Discussions similaires