Boucle très lente pour supprimer des lignes

**Mycomer** · 13/01/2016, 08h23

Bonjour,

Je cherche à supprimer d'un tableau les lignes qui ne contiennent pas certaines données en colonne A.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
' défini le nombre de lignes
    Dim lastRowQBAS As Long
    lastRowQBAS = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
    ' si Ax <> BAS ou HAUT ou BLOP ..... alors supprime ligne entière
    For LIGNE = 9 To lastRowQBAS
       If Range("A" & LIGNE).Text <> "BAS" And Range("A" & LIGNE).Text <> "BLOP" And Range("A" & LIGNE).Text <> "TRUC" And Range("A" & LIGNE).Text <> "HAUT" And Range("A" & LIGNE).Text <> "VRAI" And Range("A" & LIGNE).Text <> "FAUX" Then
           Range("A" & LIGNE).EntireRow.Delete
           LIGNE = LIGNE - 1 'sinon efface 1 résultat sur 2
       End If
    Next LIGNE

Le code marche mais est extrêmement long (tableau de plus de 10 000 lignes).
- y a-t-il un moyen de le rendre plus rapide ? (j'ai pensé à "for each", mais je n'ai pas su l'utiliser).
- peut-on simplifier la ligne "IF" du style "If Range("A" & LIGNE).Text <> "BAS" And "BLOP" And "TRUC" And ......" sans redonner le Range à chaque fois ?

Merci d'avance

**Marc-L** · 13/01/2016, 08h39

Bonjour,

c'est normal : c'est une boucle !

L'idéal étant les lignes à supprimer se suivant après un tri puis via un filtre (normal ou avancé)
pour n'afficher que les lignes à supprimer et enfin la suppression …

Autre voie : alimenter une variable tableau uniquement des éléments à conserver …

______________________________________________________________________________________________________
Je suis Paris, Charlie, …

**joe.levrai** · 13/01/2016, 08h47

Bonjour,

une autre piste :

- filtre avancé avec export dans une autre feuille des lignes ne contenant pas ces critères
- suppression de la feuille initiale

**el_slapper** · 13/01/2016, 08h54

as-tu appliqué les standards d'optimisation excel. Je cite les 2 qui te concernent(surtout le deuxième) :

Si quand vous exécutez une macro qui effectue automatiquement des modifications dans des cellules, vous constatez une lenteur étrange, c'est vraissemblablement parce que votre classeur contient de nombreuses formules, et à chaque changement quelconque d'une cellule, Excel recalcule tout. AUssi, il est sage de mettre avant toute macro l'instruction de blocage du recalcul automatique :
Application.Calculation = xlCalculationManual
Pensez à le remettre à automatique juste avant la fin de la macro :
Application.Calculation = xlCalculationAutomatic

Egalement pour des questions de vitesse d'exécution, il est conseillé de désactiver le rafraîchissement de l'écran avant la macro, et réactiver après :
Application.ScreenUpdating = False
' Faire plein de choses qui affectent le contenu des cellules
Application.ScreenUpdating = True

Si c'est encore trop lent, alors les solutions proposées ci-dessus par mes collègues sont bien plus efficaces encore(mais demandent plus de travail).

**joe.levrai** · 13/01/2016, 09h09

Re bonjour,

un petit exemple du filtre avancé, qu'il faut tester :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
Sub FiltreAvance()
Dim FEUILLE_SOURCE As Worksheet
Dim NomFeuille As String
Dim Criteres()
 
' les critères
Criteres = Array("<>BAS", "<>HAUT", "<>FAUX", "<>BLOP", "<>TRUC", "<>VRAI")
 
' la feuille de depart : IL FAUT METTRE LE NOM REEL DE TA FEUILLE
Set FEUILLE_SOURCE = ThisWorkbook.Worksheets("LaFeuilleInitiale")
 
' stockage du nom de la feuille
NomFeuille = FEUILLE_SOURCE.Name
 
' ajout d'une feuille
With ThisWorkbook.Worksheets.Add
    ' recopie de l'en-tête de la colonne A
    .Cells(1, 1).Resize(1, UBound(Criteres) + 1).Value = FEUILLE_SOURCE.Cells(1, 1).Value
 
    ' recopie des critères
    .Cells(2, 1).Resize(1, UBound(Criteres) + 1).Value = Criteres
 
    ' export via filtre avancé
    FEUILLE_SOURCE.UsedRange.AdvancedFilter xlFilterCopy, .Cells(1, 1).CurrentRegion, .Cells(4, 1), False
 
    ' nettoyage de la zone des critères
    .Cells(1, 1).Resize(4, 1).EntireRow.Delete
 
    ' suppression de la feuille source
    Application.DisplayAlerts = False: FEUILLE_SOURCE.Delete: Application.DisplayAlerts = True
 
    ' écriture du nom initiale de la feuille
    .Name = NomFeuille
End With
 
End Sub

Ps : j'ai un petit doute pour les critères VRAI et FAUX, ça va peut être cafouiller entre String et Booleen, tu nous diras

**Philippe Tulliez** · 13/01/2016, 09h11

Bonjour,
Si tu souhaites supprimer des lignes d'une liste de données suivant conditions, je te suggère comme déjà proposé par Marc et Joe, la méthode AdvancedFilter (filtre avancé) voir exemple Supprimer des lignes suivant critères avec la méthode AdvancedFilter

**Mycomer** · 13/01/2016, 09h45

Merci pour vos réponses rapides.

Mon tableau vient en effet d'un classeur externe, mais j'ai bien pensé à supprimer toutes les formules (et j'ai galéré pour ne recopier que les lignes avec les valeurs voulues, je me suis rabattu sur la solution de la copie intégrale puis suppression du "non voulu")

Je teste vos méthodes prochainement, je vous tiens au courant.

**joe.levrai** · 13/01/2016, 09h49

J'ajoute que la méthode de Philippe est beaucoup moins gourmande que la mienne !

il filtre sur place et supprime les lignes visibles (= répondant au critère)
de mon côté, j'exporte dans une nouvelle feuille pour supprimer l'ancienne.

après, pour la rapidité d'exécution, ça va se jouer en dixièmes de secondes sur 10 000 lignes, mais ça a le mérite d'être quand même plus rapide.

puisqu'il faut que je trouve un truc intéressant à ma méthode (

), je dirais que l'export du résultat permet, si besoin, de conserver la feuille initiale si finalement tu ne veux plus supprimer mais uniquement récupérer les lignes voulues.

mais pour cela, il faudra :

- mettre en commentaire la partie du code qui supprime la feuille
- changer le nom de la nouvelle feuille

EDIT :
1) si tu n'as pas besoin de conserver les formules, tu peux les remplacer par leur valeur comme ceci

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
With Workbooks("NomDuClasseur").Worksheets("NomDeLaFeuille").UsedRange
    .Value = .Value
End With

2) on peut tout à fait utiliser un filtre avancé (export ou sur place) sur un autre classeur que celui qui contient le code. Il suffit de préfixer [NOM DE FEUILLE].[PLAGE] par le nom du classeur (qui doit être ouvert en revanche)

**patricktoulon** · 19/01/2016, 22h07

Envoyé par Mycomer

Bonjour,

Je cherche à supprimer d'un tableau les lignes qui ne contiennent pas certaines données en colonne A.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Option Base 1
Sub TEST()
    Dim TABLO,virg,ias long,ro ,row_a_sup
    With ActiveSheet
        TABLO = .Range(.Cells(9, 1), .Cells(Rows.Count, 1).End(xlUp)).Value
                For i = 1 To UBound(TABLO)
           ro = i + 8
           virg = IIf(i > 1, ",", "")
                      Select Case TABLO(i, 1)
            Case "BAS", "TRUC", "BLOP", False, True
            row_a_sup = row_a_sup & virg & ro & ":" & ro
        End Select
        Next
    .Range(row_a_sup).Delete Shift:=xlUp
    End With
End Sub

**joe.levrai** · 19/01/2016, 22h31

Salut patrick

mon dieu, une boucle ??? t'as pas honte ? Et pourquoi autant de ligne de code
la même svp, sans boucle et en prenant en compte les demandes qui se sont ajoutées

tes string déclarés en variant didjou ?

c'est plus de l'indentation là, c'est de l'édentation

le travail sur un activesheet

(ouais, ça fait longtemps que je t'ai pas taquiné, j'en profite

, tes codes m'ont manqué durant ces quelques mois d'absence

)

**patricktoulon** · 19/01/2016, 23h22

re
salut joe
non je n'est pas honte j'avais envie de me detendre
cela dit
une boucle oui!! mais sur une variable tableau et la suppression de ligne se fait en une fois et sans select

re
sans boucle avec filtre

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
Sub SANS_BOUCLE()
Set plage = ActiveSheet.Range(Cells(9, 1), Cells(Rows.Count, 1).End(xlUp))
    plage.AutoFilter Field:=1, Criteria1:=Array("BAS", "BLOP", "TRUC", False, True, "VRAI", "FAUX"), Operator:=xlFilterValues
 plage.Cells.SpecialCells(xlCellTypeVisible).EntireRow.Delete
 AutoFilter = False
End Sub

**joe.levrai** · 20/01/2016, 00h00

ça y est je te retrouve

bien bourrin comme il faut, ça rallonge la durée de vie des claviers

**Mycomer** · 20/01/2016, 15h52

Joe : création des nouveaux classeurs parfaite, en revanche ça ne recopie que l'en-tête (je n'ai aucun résultat, comme si toutes les conditions étaient fausses).

PatrickToulon : ton code est nickel, c'est toujours un peu lent mais beaucoup moins que ma boucle.

J'aimerai faire tourner celui de Joe pour pouvoir comparer les temps d'exécution.

Au début je voulais juste un peu d'aide mais là j'ai l'impression d'abuser : mon en-tête est en ligne 8 mais j'ai besoin des lignes 1 à 8 et pas uniquement la 8. Joe, j'ai essayé d'utiliser le .Resize mais apparemment j'ai pas le truc

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
' écriture des en-têtes de la zone de critère
.Cells(1, 1).Resize(1, UBound(Criteres(i)) + 1).Value = FEUILLE_SOURCE.Cells(8, 1).Value 'code original
.Cells(1, 1).Resize(1, UBound(Criteres(i)) + 1).Value = FEUILLE_SOURCE.Cells(1, 1).Resize(8, 1).Value 'code modifé

Boucle très lente pour supprimer des lignes [XL-2007]

Macros et VBA Excel

Vue hybride

Discussions similaires

Partager

Partager