suppression de ligne vba excel

encom · 29 Janvier 2021

hello à tous,

voila mon problème j'ai une base de 200 milles lignes et 18 colonnes et en fonction d'une info colonne x je voudrais supprimer la ligne et ainsi de suite en testant toutes les lignes.

mon problème est que la macro tourne pendant un temps phénoménal avant d'arrivé au bout.

j'ai fais des test mais mêm avec un tableau c'est toujours super long.

Si je lance le parcoure de mon tableau il me fait le parcoure de toutes les cellules de la feuilles en 35 S mais la suppression ouch 1226 S .

j'ai peut être une erreur dans mon code ?

donc je viens quérir de l'aide

Code:

Dim tboMonArray() As Variant
Dim I As Long, J As Long
Dim pass1 As Single, pass2 As Single
Dim lastrow As Long
Dim lastcolum As Long

lastrow = Cells(Rows.Count, 1).End(xlUp).Row
lastcolum = Cells(1, Columns.Count).End(xlToLeft).Column
ReDim tboMonArray(2 To lastrow, 1 To lastcolum)

pass1 = Timer

For I = lastrow To 2 Step -1
For J = 1 To lastcolum
tboMonArray(I, J) = Cells(I, J)
 If Cells(I, 15) = "FRANCE" Or Cells(I, 15) = "SUISSE" Or Cells(I, 15) = "SWITZERLAND" Then
Rows(I).EntireRow.Delete
End If
     
Next J
Next I

Application.ScreenUpdating = True

pass2 = Timer
MsgBox pass2 - pass1 & " secondes pour" & Format(ActiveCell.CurrentRegion.Cells.Count, "#,##0") & " cellules"
Stop

merci de votre aide.

désolé j'ai pas trouve les balises de code ?!

svoglimacci · 29 Janvier 2021

Salut

encom a dit:

Les "..." juste au dessus de la fenêtre dans laquelle tu postes.

Partant du principe (que j'espère juste) que supprimer d'un coup est plus rapide, tu saurais sélectionner les lignes au fur et à mesure (je ne sais pas faire) puis demander d'un coup la suppression de la sélection ?
Partant du principe (que j'espère juste) que supprimer des lignes un peu partout oblige Excel à tout décaler au fur et à mesure ce qui est sûrement très long, tu peux peut-être trier ton tableau selon tes critères pour n'avoir qu'un range de lignes à suprimer ?

dedzix · 29 Janvier 2021

Hello,

Je pense qu'il faudrait passer par un logiciel de gestion de base de données comme access.

200 000 lignes sur excel c'est juste infâme et le logiciel n'est pas fait pour ça. Si ton fichier évolue et se complexifie j'ai bien peur qu'à terme tu ais dépensé beaucoup de temps de développement pour rien.

Sous access avec une simple requête de suppression peut faire cela facilement et le temps de traitement de la requête est également bien plus rapide.

encom · 29 Janvier 2021

hello,
je ne connais pas du tout accès après la suppréssion n'est pas le seul traitement qu'il y aura.
après je dois comparer certainne cellule avec un mapping et en fonction du test j'ajoute des colonnes avec des infos j'en supprime etc.

drul · 2 Février 2021

Salut, à quoi sert
"tboMonArray" ?

une solution qui pourrait marcher ici, serait d'effacer toutes les lignes en une seule fois:

Regarde le code ci-dessous (à adapter à ton cas évidement):

Code:

Sub test()
Dim a As Range

For i = 1 To 10
    If Cells(i, 1).Value Mod 2 = 0 Then
        If a Is Nothing Then
            Set a = Cells(i, 1)  
        Else
            Set a = Union(a, Cells(i, 1))
        End If
          
    End If
Next
a.EntireRow.Delete

End Sub

encom · 2 Février 2021

hello

mon tbomonarray c'est le nom de mon tableau

j'aurais put mettre toto ou autre.

dans ton code je vois pas le pourquoi du modulo ?

la colonne que je test contient que des lettres !

Code:

Option Base 1
Sub testtableaum()

Dim tableau()
Dim lastrow As Long
Dim lastcolum As Long
Dim ligne As Long
Dim colonne As Long
Dim counter As Long
Dim ws As Worksheet

Set ws = Sheets(2)

counter = 0

lastrow = Cells(Rows.Count, 1).End(xlUp).Row
lastcolum = Cells(1, Columns.Count).End(xlToLeft).Column


For ligne = 2 To lastrow
    If Cells(ligne, "O") <> "FRANCE" And Cells(ligne, "O") <> "SUISSE" And Cells(ligne, "O") <> "SWITZERLAND" Then
    counter = counter + 1
    ReDim Preserve tableau(lastcolum, counter)

    For colonne = 1 To lastcolum
        tableau(colonne, counter) = Cells(ligne, colonne)
    Next colonne

    End If
Next ligne

    With ws
        .Range(.Cells(1, "A"), .Cells(counter, lastcolum)) = Application.Transpose(tableau)
    End With
Stop
End Sub

j'ai fait ça en partant a l'envers en gardant avec le preserve que je veux pas.

autant sur un petit tableau ça marche son le complet ça part en vrille.

dans le with j'ai inversé colonne ligne que je transpo car ma colonne testé est toujours la même et que c'est ma ligne qui incrémente.

drul · 2 Février 2021

Re,
Le modulo, c'est juste pour un test chez moi, évidement, aucun intérêt pour toi.
Ce qui est plus intéressant dans mon exemple c'est le "UNION" qui permet d'effectuer tous les "delete" en une fois

edit: après un ou deux tests, c'est pas très concluant je trouve :/

L'idée du tableau est plaisante, par contre, j'essayerais de copier toutes les données dans un tableau au début, puis de copier les données qui t'intéresse dans un autre tableau, afin de limiter au maximum les échanges avec la spreadsheet.

svoglimacci · 2 Février 2021

svoglimacci a dit:

Donc c'est faux.
Excel a dû programmer un truc comme :
Pour chaque ligne indiquée dans le tableau
- supprime la ligne
FinPour
?

Si c'est possible :
- trier les lignes (c'est long ça ?) de manière à ce que celles à supprimer soient à la fin
- supprimer les lignes à supprimer à partir de la dernière (pour éviter le déplacement de toutes les lignes "du dessus" je sens que je ne suis pas clair*)
- re-trier pour obtenir l'ordre d'affichage requis.

*Je pense que le temps vient entre autre du fait que dans un tableau de N lignes, supprimer la ligne implique de décaler toutes les lignes i (p<i<=N) et ainsi de suite pour chaque valeur de p. Si p=N, il n'y a aucune ligne a décaler.

Qu'en penses-tu @drul ?

drul · 2 Février 2021

Mouais, je confirme toutefois le remarque de @dedzix 200000 lignes c'est trop ... mauvais usage d'excel.

svoglimacci · 2 Février 2021

Bon, test effectué, aucun gain avec mon idée de suppression descendante.
Par contre, le rafraîchissement de l'affichage consomme énormément.
Commencer la macro par
Application.ScreenUpdating = False
et la terminer par
Application.ScreenUpdating = True
a permis de diviser par 12 mon temps de traitement (non représentatif, durée trop faible).

Ce qui n'enlève rien au fait qu'une vraie base de donnée serait bien plus adaptée au besoin.

svoglimacci · 2 Février 2021

Petit essai avec plus de colonnes (10 colonnes occupées) et 50000 lignes.
Suppression des 25000 premières signes.
Avec affichage 7'36
Sans affichage 0'51
Soit un rapport de 15 ! Et avec seulement 10 colonnes.
Je me demande quel sera l'impact d'éventuelles formules (est-ce qu'il les recalcule à chaque fois parce que les cellules bougent ?)