Macro Suppression ligne très longue

[randle88]

Bonjour à tous,

Je cherche donc à optimiser la macro ci-dessous.

Pour faire simple j'ai une BDD de 100 000 lignes et 15 colonnes. En H j'ai soit 100 soit 200. Je cherche donc à supprimer tous les 100 de la BDD.

Pour l'instant la macro (récupérer sur le forum)est très très longue (quand elle ne fait pas planter l'ordinateur). Je cherche donc un moyen de l'optimiser pour la rendre productive (car à l'heure actuelle un tri manuel est plus rapide ).

A noter tout de même que en réalité le tri s'effectue sur plusieurs critères de plusieurs colonnes. C'est pour cela que je souhaiterais l'automatiser.

Merci d'avance

Dim Ligne As Long
    ActiveCell.SpecialCells(xlLastCell).Select 'dernière cellule Fichier
    Range("H" & Selection.End(xlDown).Row).Select 'dernière cellule Excel colonne A
    Ligne = Selection.End(xlUp).Row 'dernière ligne remplie en colonne A
For Ligne = Ligne To 1 Step -1
    If Range("H" & Ligne).Value = 100 Then Rows(Ligne).Delete Shift:=xlUp
Next

 

[pierrejean]

Re : Macro Suppression ligne très longue

Bonjour randle88

A tester:

Sub efface()
    ActiveSheet.Sort.SortFields.Add Key:=Range("H1"), _
        SortOn:=xlSortOnValues, Order:=xlAscending
    With ActiveSheet.Sort
        .SetRange Range("A1:O" & Range("H" & Rows.Count).End(xlUp).Row)
        .Apply
    End With
tablo = Range("H1:H" & Range("H" & Rows.Count).End(xlUp).Row)
For n = LBound(tablo, 1) To UBound(tablo, 1)
  If tablo(n, 1) = 200 Then
   fin = n
   Exit For
  End If
Next
Rows("1:" & fin).Delete
End Sub

 

[job75]

Re : Macro Suppression ligne très longue

Bonjour randle88, salut Pierre

Sub Supprime()
'la colonne auxiliaire Z est supposée non utilisée
Dim F As String
F = "=LN(RC8<>100)" 'formule à adapter si nécessaire
Application.ScreenUpdating = False
With ActiveSheet
  Intersect(.[Z:Z], .UsedRange.EntireRow).FormulaR1C1 = F
  .UsedRange.Sort .[Z:Z], xlAscending, Header:=xlNo 'tri
  On Error Resume Next 's'il n'y a pas de #NOMBRE! en colonne Z
  .[Z:Z].SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete
  .[Z:Z].ClearContents
End With
End Sub

La suppression est rapide car le tri place les lignes à supprimer en bas du tableau.

A+

 

[randle88]

Re : Macro Suppression ligne très longue

Bonjour à tous les deux,

Tout d'abord merci pour vos retours.

Je teste cela et je reviens.

 

[randle88]

Re : Macro Suppression ligne très longue

Re,

Plusieur remarque, j'essaie desesperement de comprendre le code de pierrejean

Sub efface()
    ActiveSheet.Sort.SortFields.Add Key:=Range("H1"), _
        SortOn:=xlSortOnValues, Order:=xlAscending
    With ActiveSheet.Sort
        .SetRange Range("A2:AN" & Range("H" & Rows.Count).End(xlUp).Row)
        .Apply
    End With
'Comme j'ai une ligne d'entête,je suppose que je dois partir de A2 ?
tablo = Range("H1:H" & Range("H" & Rows.Count).End(xlUp).Row)
For n = LBound(tablo, 1) To UBound(tablo, 1)
  If tablo(n, 1) = 200 Then'Là c'est si = 200 donc fin car avec le tri les 100 sont avant ?
   fin = n
   Exit For
  End If
Next
Rows("1:" & fin).Delete 'Ici je ne comprends pas le "1:" ?
End Sub

Sinon sachant qu'en réalité j'ai plusieurs code type : 100 200 300 400 500, et que je souhaite supprimer le 100 500 et 400 par exemple. Le principe "fin = n" va bloquer non ? Car les ligne à supprimer ne seront pas forcement en bas?

De plus à chaque fois que je lance la macro, ma ligne d'entête saute ? Puis je remédier à cela ?

Merci d'avance

PS : job75 merci pour ton code mais là je t'avoue qu'avec l'histoire de la colonne Z je suis un peu perdu. Si tu as le temps d'expliciter un peu.

Merci par avance

 

[pierrejean]

Re : Macro Suppression ligne très longue

Re

Mon code est très simple mais se base sur:' En H j'ai soit 100 soit 200'
Je trie pour les colonnes de A à O sur la colonne H
Ensuite ,je cherche la fin des lignes avec 100 en colonne H (1er 200)
puis j'efface les lignes de la ligne 1 a la ligne fin
Bien sur cette macro n'est plus valable si le souhait est d'effacer plusieurs valeurs
Je regarde ce qu'il est possible de faire (mais je subodore que la macro de mon ami Gerard est meilleure que la mienne)
pour gardre la ligne d'en tete :

Rows("2:" & fin).Delete

 

[randle88]

Re : Macro Suppression ligne très longue

Merci pour ces explications c'est de suite plus clair.

Concernant le code de job75 je vais me pencher dessus pour le comprendre,

Merci en tout cas

 

[randle88]

Re : Macro Suppression ligne très longue

C'est ce bout de code que j'ai du mal à appréhender :

"=LN(RC8<>100)"

Si quelqu'un à une petite explication

Merci d'avance

 

[pierrejean]

Re : Macro Suppression ligne très longue

Re

Voici une macro pour effacer les lignes avec 100 ou 400 ou 500

Sub efface()
aeff = Array(100, 400, 500) ' a adapter en valeurs et quantité
    ActiveSheet.Sort.SortFields.Add Key:=Range("H1"), _
        SortOn:=xlSortOnValues, Order:=xlAscending
    With ActiveSheet.Sort
        .SetRange Range("A1:O" & Range("H" & Rows.Count).End(xlUp).Row)
        .Apply
    End With
For m = LBound(aeff) To UBound(aeff)
tablo = Range("H1:H" & Range("H" & Rows.Count).End(xlUp).Row)
 For n = LBound(tablo, 1) To UBound(tablo, 1)
  If debut = 0 And tablo(n, 1) = aeff(m) Then
   debut = n
  Else
   If debut <> 0 And tablo(n, 1) <> aeff(m) Then
     fin = n
     Exit For
   End If
  End If
 Next
on error resume next
Rows(debut & ":" & fin).Delete
on error goto 0
debut = 0
Next m
End Sub

 

[pierrejean]

Re : Macro Suppression ligne très longue

Re

Concernant "=LN(RC8<>100)"
ceci correspond au calcul du logarithme du résultat cellule colonne H, ligne ou se trouve cette formule <>100
Si vrai =Erreur
il suffit ensuite d'effacer les lignes en erreur dans la colonne Z
Notre ami Gerard est coutumier de ces petites astuces !!!

 

[randle88]

Re : Macro Suppression ligne très longue

Merci pour toutes ces explications et la nouvelle macro.

Je vais bosser dessus.

Merci

 

[job75]

Re : Macro Suppression ligne très longue

Re,

Pour supprimer les 100, 400, 500 :

Sub Supprime()
 'la colonne auxiliaire Z est supposée non utilisée
 Dim F As String
 F = "=LN(AND(RC8<>100,RC8<>400,RC8<>500))" 'formule à adapter si nécessaire
 Application.ScreenUpdating = False
 With ActiveSheet
   Intersect(.[Z:Z], .UsedRange.EntireRow).FormulaR1C1 = F
   .UsedRange.Sort .[Z:Z], xlAscending, Header:=xlNo 'tri
   On Error Resume Next 's'il n'y a pas de #NOMBRE! en colonne Z
   .[Z:Z].SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete
   .[Z:Z].ClearContents
 End With
 End Sub

A+

 

[randle88]

Re : Macro Suppression ligne très longue

Merci job75 pour ta réponse,

Petite question, pour être sur d'avoir compris le "=LN(RC8<>100)"
j'ai tenter avec "=Si(RC8=100;NA();0)" mais cela me retourne une erreur (lors de la macro).

Pourtant si on a 100 le resultat sera une erreur et on pourra donc tier sur ce NA ? Il y a surement quelque chose que je n'ai pas percuter.

Sinon pour ma curiosité, si le texte en H est au format Texte comme une nom par exemple, la fonction LN ne pourra pas s'appliquer?

Merci d'avance

 

[pierrejean]

Re : Macro Suppression ligne très longue

Re

@ Gerard

Pour une fois
testées dans les mêmes conditions (100000 lignes en colonne H ,rien ailleurs) j'obtiens les temps suivants
ma macro: 0,7 sec
la tienne: 1,84 sec
mais il y a plus : il me reste 57143 lignes , il ne t'en reste que 57140

 

[job75]

Re : Macro Suppression ligne très longue

Re,

Pour la formule il faut la syntaxe anglaise : SI => IF, point-virgule => virgule.

En colonne H les valeurs peuvent être quelconques (textes ou nombres).

Edit : pour pierrejean

Pour les temps cela ne m'étonne pas : le calcul des formules prend du temps.

Pour le nombre de lignes supprimées je ne comprends pas...

A+