Discussion :

[Jarrod06]

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]

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]

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]

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]

Bonjour !

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]

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.

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]

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.

[Marc-L]

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 !

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

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

[Jarrod06]

@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]

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]

Voici une démonstration que j'avais aussi préparée, à coller dans le module de la feuille de calculs :

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
22
23
24
25
26
Sub Demo1()
          Dim T!, C&, R&, L&
              T = Timer
              Application.ScreenUpdating = False
    With Me.UsedRange.Rows
              C = .Count
              R = C
        While R > 11
            If .Cells(R, 2).Value = 0 Then
                     L = R
                 For R = R - 1 To 3 Step -1
                    If .Cells(R, 2).Value Then Exit For
                 Next
                     R = R + 1
                If L - R > 8 Then
                    .Item(R).ClearContents
                    .Item(R - (L < C) & ":" & L).Delete
                End If
            End If
              R = R - 1
        Wend
              C = C - .Count
    End With
              Application.ScreenUpdating = True
    MsgBox Format(C, "# ##0 lignes supprimées en ") & Format(Timer - T, "0.000s …"), vbInformation, " Demo1 :"
End Sub

___________________________________________________________________________________________________________

      Merci de cliquer sur en bas à droite de chaque message ayant aidé puis sur pour clore cette discussion …

[Jarrod06]

Bonjour à vous deux.

J'étais sur le terrain, ce qui explique ma réponse tardive.

@anthonysansh:

Je comprend ton code, à part une petite ligne:

Code :

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

1
2
 
Next i

C'est juste pour recommencer une autre boucle, une fois la première fini?

@Marc-L

Je ne comprend pas ton code, je bosse dessus et reviens vers toi, mais il semble être d'un niveau plus complexe.

En tout cas merci à vous deux pour votre aide!

[Jarrod06]

Je viens d'essayer d'appliquer vos codes à mon tableur. Mais je n'arrive pas a faire fonctionner.
Je suis la procédure suivante:

Je vais dans Dévellopeur --> Virtual basic
J'ajoute un module au seul projet que j'ai, j'y colle la ligne de code, en changeant le nom pour qu'il corresponde au nom de ma feuille.
J'appuie sur F5 pour lancer ma macro.

Mais rien ne se passe, je dois mal faire quelque chose, mais je ne vois pas quoi...

[antonysansh]

Bonjour Jarrod06,

Pour le Next i, c'est la syntaxe habituelle.
Dans une boucle pour passer à l'indice suivant on utilise Next i (si la variable de boucle est i bien sur).

Exécute le cote en pas à pas (touche F8) pour voir ce que fait le code ç chaque étape et voir pourquoi il donne l'impression de ne rien faire.

[Marc-L]

J'ajoute un module au seul projet que j'ai

Ok pour le code d'Antony mais pas pour le mien car j'ai pourtant clairement indiqué où le coller ‼

[Jarrod06]

@Anthony:

J'ai testé ton code en faisant F8 tout à l'air de fonctionner.
Le code va jusqu'à

Code :

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

1
2
 
Next i

Puis remonte à la ligne

Code :

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

1
2
 
If .Cells(i, 2) = 0 Then

Mais pour autant mon tableur excel ( le même que j'avais mis dans mon premier poste) ne change pas.

La capture d'écran que j'ai faite te montre comment j'ai fait pour ajouter mon module. Savoir si je fais cela au bon endroit.
Pour lancer mon code je clique sur l'icone que j'ai entouré en rouge, ou j'appuie sur F5, mais le tableur ne change pas, rien ne semble se passer.


@Marc-L: Mea Culpa effectivement tu l'avais écrit. J'ai donc copié ton code à la bonne place mais il semble y avoir une "Erreur de compilation" ligne 5 "Utilisation incorrect du mot clé Me".

[antonysansh]

If .Cells(i, 2) = 0 Then sert a tester s'il y a un 0 dans la colonne B.
T'es 0 sont en colonne C donc tu dois juste corriger cette ligne.

[Jarrod06]

J'ai changé la petite erreur, et ca marche!!!!
Mais malheureusement, le traitement n'est pas satisfaisant.
J'ai fait un traitement manuel de mon tableur pour pouvoir le comparer avec le traitement avec le code.

A gauche mon traitement manuel et a droite celui fait par le code

Avec le code il manque des données (1922 lignes avec le traitement manuel et 1235 lignes avec le code.) de plus le code supprime des lignes comportant des valeurs (cf PJ). Depuis ce matin je relis ton code et tout le reste, mais ne trouve pas de ou peux provenir l'erreur...

[Marc-L]

Pas de souci avec ma démonstration du post #11 supprimant 17 023 lignes en 0,5s
il reste 1 959 lignes avec le classeur joint dans la présentation initiale …

[Jarrod06]

Pas de souci avec ma démonstration du post #13 supprimant 17 023 lignes en 0,5s
il reste 1 959 lignes avec le classeur joint dans la présentation initiale …

Tu n'as pas du voir mon post ou je te disais que ton code me mettait un message d'erreur. J'ai vérifié je l'ai collé au bon endroit.
Voici le message d'erreur: