Supprimer des données avec conditions

**Jarrod06** · 15/05/2017, 06h03

Bonjour à tous.

Je réalise actuellement mon stage de fin d'étude pour une ONG en Nouvelle Calédonie. Dans le cadre de se stage je dois traiter des grandes quantités de valeur sous Excel (jusqu'à 20000 lignes par tableur), je comptai faire un VBA pour traiter rapidement mes données. Depuis deux jours je lis des tutoriels, comme celui-ci: http://laurent-ott.developpez.com/tu...a-tome-1/#LVII et apprend à réaliser des VBAs "basique" mais je ne trouve toujours pas de solution et les données commencent à s'accumuler de façon alarmante. Je viens donc vous demander votre aide (En toute humilité...)

Mon problème est le suivant:

Mon tableur se présente en 2 colonne: la première "date" sous la forme JJ/MM/AAAA hh:mm et une deuxième "pluie" avec des chiffres à une seule décimale. La deuxième colonne est donc une chronique de pluie, et la première les dates qui sont associées aux prélèvements. Dans mon protocole de traitement de donnée on sépare les épisodes pluvieux s'il y a une interruption de la pluie d'au moins 1 heure, soit 10 lignes du tableur.

J'aimerai donc (en partant du premier prélèvement) supprimer les lignes ou j'ai au moins 10 fois de suite les valeurs 0 est les remplacer par une seule ligne, qui me servirait de séparateur, pour faciliter la lisibilité " post-traitement". J’espère que mon post est clair, je vous poste mon fichier excel en PJ

( Avec le décalage horaire je risque de mettre un peu de temps à répondre)

Merci d'avance Nico

Pluvio_haut_test.xlsx

**antonysansh** · 15/05/2017, 08h35

Bonjour Jarrod06,

Je vais t'expliquer comment je vois le code :
Je vais faire une boucle sur toutes les lignes (du bas vers le haut pour éviter les problèmes d'indice de ligne quand je vais en supprimer).
Pour chaque ligne, je regarde s'il y a un 0. Si 0 alors j'incrémente une variable NombreDeZero sinon je vais regarder combien de 0 je viens compter.
Si mon nombre de 0 est >= 10, je supprimer les lignes et insert une ligne vide.

Voila le code (en français) :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
Pour i de NombreLignes a 1 tous les -1
    Si 0 alors
        NbZero = NbZero + 1
    Sinon
        Si NbZero >= 10 alors
            Lignes(i+1 a i+NbZero).Supprimer
            Lignes(i+1).Insert
            NbZero=0
        Fin Si
    Fin Si
Prochain i

Pour traduire ce code en VBA, tu vas devoir utiliser des instructions simple donc les tutos que tu as pu lire doivent être suffisant.
D'avance je te conseil d'aller voir vers les bloc With et la propriété Application.ScreenUpdating qui vont t'aider a faire un code plus propre.

Je te laisse le traduire en code VBA et poster ton code ici. Nous t'aiderons à l'améliorer

**Jarrod06** · 15/05/2017, 09h53

Merci de ta réponse.
J'avais commencé a faire un code je te met mon "brouillon"

Code :

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

1
2
3
4
5
 
   Sub Pluie ()
     Dim Pluie As Double ' car c'est un nombre a virgule
       Pluie=1 'Pour éviter un code d'erreur a priori
While Cells (Pluie,1)<> "0"

Je t'avoue qu'à la vue de tes lignes de code je vois mal comment faire le lien, je suis novice dans l'utilisation des VBA. J'ai principalement travaillé avec des logiciels comme R sur des traitements beaucoup plus simple, ne nécessitant pas des connaissances très poussées.
Aurais-tu des liens de tutoriel " adaptés" a mon cas?

**antonysansh** · 15/05/2017, 10h22

Pas besoin de Dim Pluie As Double je pense.

Tu ne vas pas stocker la valeur de la deuxième colonne mais seulement tester si elle est égale a 0 (donc pas besoin de variable pour ça).
Pas besoin de While non plus car on test si égale 0 sur chaque ligne.

Du devrais plutôt essayer de traduire en VBA le code que je t'ai proposé (le tuto dont tu as mis le lien est suffisant).
Tu dois juste utiliser une boucle For avec Step -1 (de bas en haut sur les ligne)
Ensuite un test dans un IF et second test dans le premier IF.

Fait l'exercice de traduction et je compléterais ou modifierais ton code

**Marc-L** · 15/05/2017, 12h31

Bonjour !

Envoyé par Jarrod06

J'aimerai donc (en partant du premier prélèvement) supprimer les lignes ou j'ai au moins 10 fois de suite les valeurs 0 est les remplacer par une seule ligne, qui me servirait de séparateur, pour faciliter la lisibilité " post-traitement". J’espère que mon post est clair

Pas clair … Logiquement s'il y a au moins 10 lignes se suivant à 0 alors

il suffit juste de les supprimer toutes sauf une ! Encore faudrait-il savoir laquelle garder …

Mais comme il est question de les remplacer, alors par quoi ?!

Dans le cas d'un grand nombre de données à traiter avec des lignes discontinues à supprimer,
si la suppression de ces lignes prend trop de temps, alors revenir au B-A-BA d'Excel
- en général une colonne supplémentaire indiquant les lignes à garder et celles à supprimer -
car déjà en opérant manuellement ce serait plus rapide qu'un code ramant avec une boucle supprimant ligne à ligne !

On verra cela plus tard si nécessaire, Anthony étant déjà sur sa lancée …

___________________________________________________________________________________________________________

Je suis Paris, Egypte, Stockholm, London, Istanbul, Berlin, Nice, Bruxelles, Charlie, …

**Jarrod06** · 16/05/2017, 02h00

Envoyé par antonysansh

Pas besoin de Dim Pluie As Double je pense.

Tu ne vas pas stocker la valeur de la deuxième colonne mais seulement tester si elle est égale a 0 (donc pas besoin de variable pour ça).
Pas besoin de While non plus car on test si égale 0 sur chaque ligne.

Du devrais plutôt essayer de traduire en VBA le code que je t'ai proposé (le tuto dont tu as mis le lien est suffisant).
Tu dois juste utiliser une boucle For avec Step -1 (de bas en haut sur les ligne)
Ensuite un test dans un IF et second test dans le premier IF.

Fait l'exercice de traduction et je compléterais ou modifierais ton code

Alors voila le code traduit:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
Sub Pluie()
  For i=n to 1 Step-1
    If 0 Then
      Nb0=Nb0 + 1
    Else
      If Nb0>= 10 Then
         Ligne(i+1 To +Nb0).Delete
         Ligne(i+1).Insert
         Nb0=0
      End If
    End If
End Sub

J'espère me rapprocher de la solution, et avoir compris le principe de ce code.

Envoyé par Marc-L

Bonjour !

Pas clair … Logiquement s'il y a au moins 10 lignes se suivant à 0 alors

il suffit juste de les supprimer toutes sauf une ! Encore faudrait-il savoir laquelle garder …

Mais comme il est question de les remplacer, alors par quoi ?!

Dans le cas d'un grand nombre de données à traiter avec des lignes discontinues à supprimer,
si la suppression de ces lignes prend trop de temps, alors revenir au B-A-BA d'Excel
- en général une colonne supplémentaire indiquant les lignes à garder et celles à supprimer -
car déjà en opérant manuellement ce serait plus rapide qu'un code ramant avec une boucle supprimant ligne à ligne !

On verra cela plus tard si nécessaire, Anthony étant déjà sur sa lancée …

___________________________________________________________________________________________________________

Je suis Paris, Egypte, Stockholm, London, Istanbul, Berlin, Nice, Bruxelles, Charlie, …

Je voudrai remplacer les lignes supprimées par un ligne "vierge" qui aurait le rôle de séparateur permettant une lecture plus claire des tableurs lors du traitement.

Si j'étais le seul à faire ce traitement je ferais une suppression manuelle, ça serait long et répétitif mais au moins je n'aurai pas à passer par l'étape programmation. Dans le cadre de mon stage je veux créer un système automatisé. A la fin de mon séjour, la personne reprenant mon travail sera un local, avec un bagage informatique faible, (niveau lycée, voir collège). Donc, pour pérenniser le projet, je veux lui faciliter au maximum le travail, et je pense que la mise au point de ce code est une bonne solution. Après je reste à l'écoute de vos propositions!

**antonysansh** · 16/05/2017, 08h04

Bonjour Jarrod06,

Ok pour la boucle et pour les IF imbriqués.
Par contre tu aurais au moins pu déclarer les variables

Voila une proposition de déclaration des variables :

Code :

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

1
2
3
    Dim i As Long               'Indice de boucle
    Dim DerniereLigne As Long   'Pour stocker le numero de la derniere ligne
    Dim NBzero As Long          'compteur pour les zero

Pour calculer DerniereLigne :

Code :

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

DerniereLigne  = ThisWorkbook.Sheets("NomFeuille").Cells(Rows.Count, 1).End(xlUp).Row

Tu trouveras plein d'info sur le forum pour comprendre cette ligne.

Ensuite dans ce que tu as écris il y a trois choses qui ne vont pas :

Ligne n'existe pas. En VBA c'est Row plus particulièrement Rows ici car c'est a la collection des lignes que nous nous intéressons.
Rows seul n'est pas suffisant, tu dois ajouter devant, le classeur et la feuille concernés. Donc ThisWorkbook.Sheets("NomFeuille").Rows...
Rows(1 to 10) n'est pas une syntaxe correct. C'est Rows(1:10) qui est correct. Donc dans notre cas .Rows(i + 1 & ":" & i + NBzero)

Tu as maintenant tous les éléments pour écrire la macro

Une fois écrite, tu verras que sont déroulement n'est pas très jolie pour les yeux. Donc regarde le fonctionnement de :
Application.ScreenUpdating = False et Application.ScreenUpdating = True

On est sur la voie, encore un retour de ta part en prenant compte de mes remarques et tu auras un code fonctionnel.

**Jarrod06** · 17/05/2017, 05h15

@antonysansh
Je t'avais mal compris pour les variables. Dans mon premier "brouillon" j'avais commencé à les déclarer, et en voyant ta réponse, je pensais qu'avec mon objectif final ça n'était pas nécessaire.
Voici le code:

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
Sub Pluie()
  Application.ScreenUpdating= False 'Pour figer l'écran
    Dim i As Long
    Dim DerniereLigne As Long
        DerniereLigne= ThisWorkbook.Sheets("Feuil_01").Cells(Rows.Coun, 1).End(xlUp).Row
    Dim NBzero As Long
  For i= n to 1 Step -1
     If 0 Then
  NBzero= NBzero + 1
     Else
     If NBzero>= 10 Then
        Worksheets("Feuil_01").Rows(i+1 & ":" & i+NBzero).Delete
        Worksheets("Feuil_01").Rows(i+1).Insert
     NBzero= 0
     End If
     End If
  Application.ScreenUpdating= True 'Reactive l'écran
End Sub

Juste par curiosité, j'ai vu sur un tutoriel, que pour alléger le code on pouvait remplacer Workbook.Sheets par Worksheets, est-ce vrai?
Voila j'espère avoir bien mis en pratique tes conseils.

**antonysansh** · 17/05/2017, 08h19

Bonjour,

@Marc
Mea culpa pour le Rows(1:10) répondu rapidement pour indiquer que Rows(1 to 10) n'est pas correct. Merci de ta correction

@Jarrod06
Le code que tu as posté commence a ressemblé a ce que j'ai en tête depuis le début.
Je te poste le code que j'ai écris après ton premier post :

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
Sub test()
    Dim i As Long               'Indice de boucle
    Dim DerniereLigne As Long   'Pour stocker le numero de la derniere ligne
    Dim NBzero As Long          'compteur pour les zero
    Application.ScreenUpdating = False
        With ThisWorkbook.Sheets("NomFeuille")
            DerniereLigne = .Cells(Rows.Count, 1).End(xlUp).Row
            For i = DerniereLigne To 1 Step -1
                If .Cells(i, 2) = 0 Then
                    NBzero = NBzero + 1
                Else
                    If NBzero >= 10 Then
                        .Rows(i + 1 & ":" & i + NBzero).Delete Shift:=xlUp
                        .Rows(i + 1).Insert Shift:=xlDown
                        NBzero = 0
                    End If
                End If
            Next i
        End With
    Application.ScreenUpdating = True
End Sub

Nous sommes donc sur le même code à quelques détails près :

Par convention, toutes les variables doivent être déclarées juste après la ligne Sub NomMacro()
La boucle n'est pas For n to ... mais For DerniereLigne to ... (si la variable DerniereLigne dans ton code ne sert à rien)
Le premier test : If 0 then ne peut pas fonctionner. Enfin si, mais c'est comme écrire If FAUX = VRAI then donc le test n'est jamais satisfait.
Tu dois donc écrire If ThisWorkbook.Sheets("NomFeuille").Cells(i,2) = 0 then
J'utilise un bloc With pour éviter de toujours écrire ThisWorkbook.Sheets("NomFeuille")

Le bloc With :
Regarde sur le forum des infos sur le sujet.
Je te mets quand même un exemple pour illustrer son utilité :

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
Sub SansWith()
    ThisWorkbook.Sheets("Feuil1").Range("A1") = "test1"
    ThisWorkbook.Sheets("Feuil1").Range("B2") = "test2"
    ThisWorkbook.Sheets("Feuil1").Range("C3") = "test3"
    ThisWorkbook.Sheets("Feuil1").Range("D4") = "test4"
End Sub
 
Sub AvecWith()
    With ThisWorkbook.Sheets("Feuil1")
        .Range("A1") = "test1"
        .Range("B2") = "test2"
        .Range("C3") = "test3"
        .Range("D4") = "test4"
    End With
End Sub

Le . est très important dans le bloc With

**Marc-L** · 16/05/2017, 12h01

Envoyé par Jarrod06

Après je reste à l'écoute de vos propositions!

Si le classeur joint en exemple est assez représentatif
alors une classique remontée pour supprimer les données fonctionne déjà en moins d'une seconde.

Pour éviter de ralentir la procédure ne pas insérer de ligne mais juste en effacer une …

Salut Antony !

Envoyé par antonysansh

C'est Rows(1:10) qui est correct

La syntaxe correcte est Rows("1:10") …