Supprimer ligne

Bonjour à tous,

Je dispose d'un fichier annuaire avec pas mal de lignes. Certaines ne sont pas renseignées notamment la colonne B.

J'aimerais supprimer les lignes dont la colonne B comporte un "x".

Comment faire par macro?

merci

oza

Bonjour,

Essaie avec ce code à placer dans un module

Sub test()
'Macro dan
Dim i As Integer
With ActiveSheet
    For i = .Range("B" & Rows.Count).End(xlUp).Row To 2 Step -1
        If UCase(.Cells(i, 2)) = "X" Then Rows(i).Delete
    Next i
End With
End Sub

Amicalement

merci Dan!

je débute, j'avais pensé à ça:

Sub supprimer()
    Dim temp As Boolean
    For i = 1 To 10000
    If Cells(i, 2) = "x" Then
        Rows(i).Delete
    End If
    Next
End Sub

qu'en penses tu? les deux fonctionnent

Bonjour à tous,

Plus rapide avec un filtre

Sub SupprLignes()
Dim Lg%
    Application.ScreenUpdating = False
        On Error Resume Next
        ActiveSheet.ShowAllData 'libère le fitre"
        On Error GoTo 0
    Lg = Range("a" & Rows.Count).End(xlUp).Row
    Range("k2") = "=OR(b2="""",b2=""x"")" 'critère
    Range("a1:c" & Lg).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
    Range("k1:k2"), Unique:=False
    Range("k2").ClearContents
    Range("a2:a" & Lg).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        On Error Resume Next
        ActiveSheet.ShowAllData
End Sub

Amicalement

Claude

Bonjour à tous,

ozaland, pour ta culture perso, quelques conseils pour optimiser ton code.

1- Si tu n'utilises pas la variable temp (très utile ce surlignage ) dans ton code, il est inutile de la déclarer

2- Conseil qui découle donc du premier, déclare les variables que tu utilises (i, derlign)

3- Pour améliorer le temps d'exécution de ton code, tu peux désactiver la mise à jour de l'écran en utilisant Application.ScreenUpdating = False

4- Au lieu de mettre la limite à 10000, tu peux utiliser une instruction du genre End(xlUp).Row pour détecter la dernière ligne.

5- Le traitement de la première ligne ne sert pas ici.

Donc une optimisation possible de ton code :

Sub supprimer()
Dim i As Long, derlign As Long
Application.ScreenUpdating = False
derlign = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To derlign
    If Cells(i, 2) = "x" Then Rows(i).Delete
Next
End Sub

Ensuite, un conseil plus général. Le fait de manipuler directement les cellules devient très vite un problème (lenteur d'exécution) dès que ta base de données comporte un nombre important de ligne.

Une solution est de passer par des tableaux intermédiaires.

L'exécution du code suivant est presque instantanée pour une base de 60000 lignes.

Sub SuppX()
Dim derlign As Long, i&, j&, lim&
Dim valComp As String
Dim tabloIni, temp

Application.ScreenUpdating = False
derlign = Range("A" & Rows.Count).End(xlUp).Row
tabloIni = Range("A2:C" & derlign).Value
lim = UBound(tabloIni, 1)
ReDim temp(1 To lim, 1 To 3)
j = 1

For i = 1 To lim
    valComp = tabloIni(i, 2)
    If valComp <> "x" Then
        temp(j, 1) = tabloIni(i, 1)
        temp(j, 2) = valComp
        temp(j, 3) = tabloIni(i, 3)
        j = j + 1
    End If
Next i
[a2].Resize(lim, 3).Value = temp
End Sub

merci dubois, vba-new!

effectivement j'ai commis quelques grossières erreurs dans mon code..

vos macros fonctionnent parfaitement, je tacherai de les adapter correctement sur mon fichier.

merci

oza

-- 27 Juil 2011, 10:41 --

mon tableau est en réalité sur 34 colonnes A:AH

la première ligne remplie est sur la 3.

j'ai essayé de modifier le code mais j'éprouve quelques difficultés, pouvez vous m'aider?

merci

Sub SuppX()
Dim derlign As Long, i&, j&, lim&
Dim valComp As String
Dim tabloIni, temp

Application.ScreenUpdating = False
derlign = Range("A" & Rows.Count).End(xlUp).Row
tabloIni = Range("A3:AH" & derlign).Value
lim = UBound(tabloIni, 1)
ReDim temp(1 To lim, 1 To 34)
j = 1

For i = 1 To lim
    valComp = tabloIni(i, 2)
    If valComp <> "x" Then
        temp(j, 1) = tabloIni(i, 1)
        temp(j, 2) = valComp
        temp(j, 34) = tabloIni(i, 34)
        j = j + 1
    End If
Next i
[a3].Resize(lim, 34).Value = temp
End Sub

re,

toujours avec filtre,

si les en-têtes sont en ligne 2

Sub SupprLignes()
Dim Lg%
    Application.ScreenUpdating = False
        On Error Resume Next
        ActiveSheet.ShowAllData 'libère le fitre"
        On Error GoTo 0
    Lg = Range("a" & Rows.Count).End(xlUp).Row
    Range("ak2") = "=OR(b3="""",b3=""x"")" 'critère
    Range("a2:ah" & Lg).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
    Range("ak1:ak2"), Unique:=False
    Range("ak2").ClearContents
    Range("a3:a" & Lg).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        On Error Resume Next
        ActiveSheet.ShowAllData
End Sub

édit: code modifié critère en ak2 au lieu de k2

Claude

Bonjour,

Avec 34 colonnes et beaucoup de lignes, la méthode par filtre risque d'être assez longue Claude.

Voici un code optimisé par rapport au code que j'ai posté plus haut oza :

Sub SuppX_v2()
    Dim nbLign As Long, i&, j&, Ligne1&, nbCol&, Col&
    Dim tabloIni

    Application.ScreenUpdating = False
    Ligne1 = 3 '<--- première ligne remplie
    nbCol = 34 '<--- nombre de colonnes que comporte la BDD
    nbLign = Range("A" & Rows.Count).End(xlUp).Row - Ligne1 + 1
    With Cells(Ligne1, 1).Resize(nbLign, nbCol)
        tabloIni = .Value
        .ClearContents
    End With
    j = 0
    For i = 1 To nbLign
        If tabloIni(i, 2) <> "x" Then
            j = j + 1
            For Col = 1 To nbCol: tabloIni(j, Col) = tabloIni(i, Col): Next Col
        End If
    Next i
    Cells(Ligne1, 1).Resize(j, nbCol).Value = tabloIni
End Sub

Dans le code, tu mets le numéro de la première ligne remplie et le nombre de colonnes.

Re,

ozaland a écrit :

merci Dan!

je débute, j'avais pensé à ça:

Sub supprimer()
    Dim temp As Boolean
    For i = 1 To 10000
    If Cells(i, 2) = "x" Then
        Rows(i).Delete
    End If
    Next
End Sub

qu'en penses tu? les deux fonctionnent

Déso j'avais zappé ta demande.

Ton code te donne le même résultat que mon code mais à la différence que tu vas jusque 10000 lignes alors que ce que je te propose ne tient compte que des lignes complétées dans ta colonne B. donc que tu sois à la ligne 11000 ou à la ligne 20, le code en tiendra compte.

Si tu as autant de lignes (10000 !!), le code que je t'ai proposé prendra un certain temps. Là soit tu passes par les solutions proposées par Dubois ou VBAnew mais tu peux aussi prendre ce code qui réagit instantanément.

Sub test()
'Macro Dan
Application.DisplayAlerts = False
On Error Resume Next
ActiveSheet.ShowAllData 'libère le fitre"
On Error GoTo 0
Range("A1").AutoFilter Field:=2, Criteria1:="x"
If Range("A" & Rows.Count).End(xlUp).Row > 1 Then
Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Delete
End If
ActiveSheet.ShowAllData
End Sub

Amicalement

edit Dan : Barré ce qui n'est pas correct. Voir mon intervention plus loin dans le fil

Bonjour à tous,

vba-new,

effectivement, je viens de tester sur 61500 lignes et 34 colonnes (fichier de 15 Mo),

le temps de traitement par filtre (Auto ou élaboré) = 52 secondes

ton dernier code fait la même chose en seulement 7 secondes

Cependant, j'ai encore du mal à assimiler cette histoire de "tabloIni"

Je vois qu'au départ, on met tout le tableau en mémoire (en quelque sorte) et qu'on le

reconstruit suivant la demande.

Pour m'aider à comprendre, quel aurait été le code pour colorer en jaune les cellules avec un "x"

en colonne "B", au lieu de supprimer la ligne ?

En comparant les 2 codes, j'arriverais peut-être à piger !

c'est un peu hors du sujet, mais aidera pour la compréhension .

Bonne journée

Amicalement

Claude

Salut à tous

Comme d'habitude, je parcours les sujets pour avoir des idées, et des codes. Cependant dans les codes donnés, certaines choses me posent problèmes. Soit j'ai mis le doigt sur des "erreurs", soit j'ai encore à apprendre. Merci de m'aider à combler mes lacunes

Dan

Sub test()
'Macro Dan
Application.DisplayAlerts = False
On Error Resume Next
ActiveSheet.ShowAllData 'libère le fitre"
On Error GoTo 0
Range("A1").AutoFilter Field:=2, Criteria1:="x"
If Range("A" & Rows.Count).End(xlUp).Row > 1 Then
Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Delete
End If
ActiveSheet.ShowAllData
End Sub

C'est un filtrage, avec élimination du résultat => ça je comprends.

Ce qui me pose problème, c'est "Application.DisplayAlerts = False" : on élimine les messages intempestifs d'erreurs. Mais ça me suscite deux question :

• Pourquoi ? À quoi il sert ? À éliminer la confirmation, suite au delete ?
• Qu'est-ce qui remet le "DisplayAlerts" en route ?

ozaland

Sub supprimer()
        Dim temp As Boolean
        For i = 1 To 10000
        If Cells(i, 2) = "x" Then
            Rows(i).Delete
        End If
        Next
    End Sub

qu'en penses tu? les deux fonctionnent

Pour t'en convaincre, fait une plage avec un "x" de la ligne 1 à la ligne 100 et appliques-y deux ou trois fois ta macro en regardant ce que ça donne, puis finit par appliquer une fois celle de Dan.

A+

re,

Gorfael, comme tu es passé par là.

C'est un filtrage, avec élimination du résultat => ça je comprends.

Ce qui me pose problème, c'est "Application.DisplayAlerts = False" : on élimine les messages intempestifs d'erreurs. Mais ça me suscite deux question :

• Pourquoi ? À quoi il sert ? À éliminer la confirmation, suite au delete ?
• Qu'est-ce qui remet le "DisplayAlerts" en route ?

testé sur MAC le fait de mettre l'instruction à False n'oblige pas à remettre à True car au sortir de la macro, le True est remis automatiquement par défaut. A vérifier sur PC. sinon rajouter Application.DisplayAlerts = True à la fin de code

Quand à mon commentaire sur le résultat de mon code et de celui de Ozaland, ... un peu trop vite effectivement.

Ozaland, lorsque tu supprimes les lignes il faut partir du bas vers le haut. Sans quoi tu conserveras des "X" dans ta base lorsque notamment dans l'exécution du code si deux ou plusieurs X se suivent.

Gorfael, ce serait bien de nous donner tes inputs sur les autres codes proposés aussi. Mais là peut être qu'il n'y a rien à dire.

Bonjour à tous,

@Claude :

dubois a écrit :

Cependant, j'ai encore du mal à assimiler cette histoire de "tabloIni"

Je vois qu'au départ, on met tout le tableau en mémoire (en quelque sorte) et qu'on le

reconstruit suivant la demande.

dubois a écrit :

Pour m'aider à comprendre, quel aurait été le code pour colorer en jaune les cellules avec un "x"

en colonne "B", au lieu de supprimer la ligne ?

Un autre point : tu dis que je supprime la ligne. Ce n'est pas tout à fait ça, en fait je recrée un tableau dans lequel j'omets de mettre les lignes qui contiennent le "x".

Ce qui complique un peu la chose dans mon code est que j'écrase les données du tableau initial (tabloIni) pour éviter de passer par un autre tableau intermédiaire.

Pas sûr que ce soit très clair...

Salut Dan et le forum

Gorfael, ce serait bien de nous donner tes inputs sur les autres codes proposés aussi. Mais là peut être qu'il n'y a rien à dire.

Je pencherais plus par des habitudes, et comme chacun à ses habitudes propres...

J'ai "critiqué" uniquement parce que je voulais me greffer sur la discussion vba-new / dubois et que je ne voulais pas n'être qu'un parasite. Et comme j'avais une question...

D'après ce que je viens de tester, ScreenUpDating et DisplayAlerts sont remis à True à la fin de la macro. Mais je préfères quand même les mettre dans mes codes, ça me semble plus clair : je ne change pas ses habitudes comme ça

A+

Bonjour à tous,

Réflexion sur divers codes (suite macro de vba-new)

le "tabloIni" à beau être virtuel, j'ai du mal à avaler que çà boucle sur toutes les cellules en

si peu de temps ! , mais bon, je fais confiance en la méthode

Je vais analyser de + près cette méthode et tenter de la mettre en pratique.

---- remarque sur le filtre élaboré ----

en extrayant les résultats sur une autre feuille nommée "Extrait", le temps de 52 secondes devient 1 seconde

(55000 lignes extraites sur 61500) sur 34 colonnes

le code deviendrait:

Sub ExtraitLignes()
Dim Lg&, x
    x = Time
    Application.ScreenUpdating = False

    Lg = Range("a" & Rows.Count).End(xlUp).Row
    Range("ak2") = "=b3<>""x""" 'critère (inversé)

    Range("a2:ah" & Lg).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
    Range("ak1:ak2"), CopyToRange:=Sheets("Extrait").Range("a2:ah2"), Unique:=False

    Range("ak2").ClearContents
    MsgBox ("temps macro = " & Format(Time - x, "hh:mm:ss"))
End Sub

Maintenant, tout dépend de ce qu'on veut faire par la suite et choisir la bonne méthode.

Bonne journée à tous

Amicalement

Claude

Re,

Et Oui Claude, le filtre avancé que tu utilises beaucoup dans tes codes est très puissant.

ce serait bien intéressant de voir quel est le code le plus rapide dans tous ceux proposés ici.

Amicalement

Bonjour à tous,

Dan a écrit:

ce serait bien intéressant de voir quel est le code le plus rapide dans tous ceux proposés ici.

En ajoutant un chrono à chaque macro

Fichier de 61560 lignes dont 6480 à supprimer (avec "x" en colonne "C"), le tout sur 34 colonnes,

testé sur les 4 macros proposées

1) filtre avancé sur place de claude => 52 secondes

2) filtre Auto sur place de Dan => 52 secondes

3) solution avec Tableau temporaire de vba-new => 7 secondes

4) filtre avancé avec extraction sur feuille "Extrait" => 1 seconde

j'en conclus que sur les filtre sur place, c'est cette ligne qui prend le + de temps

Range("a2:a" & Lg).SpecialCells(xlCellTypeVisible).EntireRow.Delete

à noter qu'avec 3) et 4), on perd les formules (s'il y en a) et sans doute les MFC ?.

Comme dit précédemment, voir le contexte pour choisir la méthode

Exemple: pour faire des Stats ou graphiques, j'opterai pour la 4)

voilou !

Amicalement

Claude

Bonjour à tous,

Effectivement, le filtre avancé sur autre feuille est très rapide !

Voici encore une autre solution, avec une colonne intermédiaire cette fois-ci. Plus rapide que le code que j'ai proposé (dont le temps d'exécution est proportionnel au nombre de lignes et colonnes) :

Sub SuppLignColInter()
    Dim derlign As Long

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    derlign = Cells.Find("*", , , , xlByRows, xlPrevious).Row

    Columns("a:a").Insert
    With Range("a2:a" & derlign)
        .FormulaR1C1 = "=IF(RC3=""x"",1,0)"
        .Value = .Value
        .CurrentRegion.Sort [a2], 1, Header:=1
        .Replace "1", "", 2
        On Error Resume Next
        .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        .EntireColumn.Delete
    End With
    Application.Calculation = xlCalculationAutomatic
End Sub

• On ajoute une colonne dans laquelle on met 1 s'il y a un "x" et 0 dans le cas contraire.
• On enlève les formules.
• On trie pour avoir les lignes contenant 1 à la fin
• On remplace les 1 par rien
• Et on supprime les lignes dont la colonne A est vide.

Ce code est rapide car on regroupe toutes les lignes contenant un "x" à la fin et on supprime en un coup. Le temps d'exécution pour 60000 lignes (dont environ 20000 à enlever) et 34 colonnes est d'environ 2 secondes.

A tester sur ton fichier Claude pour comparer le temps d'exécution.

re vba-new,

Je confirme,

ce dernier code fait 2 secondes (dans les mêmes conditions)

ce serait donc le + efficace, on altère pas les données et formats.

Claude