suppression de ligne vba excel

encom

Nouveau membre
#1
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 ?!
 
Dernière édition:

svoglimacci

check memory failed but no bug detected
#2
Salut :)
désolé j'ai pas trouve les balises de code ?!
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

Grand Maître
#3
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

Nouveau membre
#4
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

Obscur pro du hardware
Staff
#5
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

Nouveau membre
#6
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

Obscur pro du hardware
Staff
#7
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.
 
Dernière édition:

svoglimacci

check memory failed but no bug detected
#8
Partant du principe (que j'espère juste) que supprimer d'un coup est plus rapide
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

Obscur pro du hardware
Staff
#9
Mouais, je confirme toutefois le remarque de @dedzix 200000 lignes c'est trop ... mauvais usage d'excel.
 

svoglimacci

check memory failed but no bug detected
#10
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

check memory failed but no bug detected
#11
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 ?)
 
Vous devez vous inscrire ou vous connecter pour répondre ici.
Membres en ligne
  • holivman
  • longaripa
Derniers messages publiés
Statistiques globales
Discussions
844 496
Messages
7 563 441
Membres
1 583 026
Dernier membre
holivman
Partager cette page
Haut