Variable dynamique sur boucle For Each

Bonjour à Tous,

Je voudrais supprimer les lignes vides sur les plages définies par mois sur chacune de mes feuilles mensuels en une seule fois.

Sur chacune des feuilles j'ai le nom de ma variable en cellule G1

Avant que j'insère la boucle For Each, la macro fonctionnait sur chaque feuille séparément mais je pense que j'ai une très mauvaise syntaxe... je viens d'essayer de rajouter un with... mais ça n'a pas l'air d'être mieux...

Si vous avez le temps de regarder...

Merci beaucoup à Tous

Je ne peux malheureusement pas vous transmettre mon fichier, il est beaucoup trop lourd.

Chloé

Sub Supprimer_LignesVides_Toutes_Feuilles()

'Bouton Supprimer les lignes vides

For Each sh In Array("Feuille de Temps Janvier", "Feuille de Temps Février", _
        "Feuille de Temps Mars", "Feuille de Temps Avril", "Feuille de Temps Mai", _
        "Feuille de Temps Juin", "Feuille de Temps Juillet", "Feuille de Temps Aout", _
        "Feuille de Temps Septembre", "Feuille de Temps Octobre", _
        "Feuille de Temps Novembre", "Feuille de Temps Décembre")

Dim mois As String

With sh
mois = sh.Range("G1").Value

Dim I As Integer
    For I = Range(mois).Rows.Count To 1 Step -1

        If WorksheetFunction.CountBlank(Range(mois).ListObject.ListRows(I).Range) = Range(mois).Columns.Count 
        Then Range(mois).ListObject.ListRows(I).Delete
        End If

Next I

End With

Next sh

End Sub

Bonjour,

Le then doit être au bout dans ce cas ci.

        If WorksheetFunction.CountBlank(Range(mois).ListObject.ListRows(I).Range) = Range(mois).Columns.Count then
        Range(mois).ListObject.ListRows(I).Delete
        End If

Bonjour Chlocie,

Peut-être ainsi....

Sub test()
Dim sh As Worksheet, mois As String, I As Integer

For Each sh In ThisWorkbook.Sheets
 mois = sh.Range("G1").Value

  With sh
    Select Case .Name
      Case "Feuille de Temps Janvier", "Feuille de Temps Février", _
        "Feuille de Temps Mars", "Feuille de Temps Avril", "Feuille de Temps Mai", _
        "Feuille de Temps Juin", "Feuille de Temps Juillet", "Feuille de Temps Aout", _
        "Feuille de Temps Septembre", "Feuille de Temps Octobre", _
        "Feuille de Temps Novembre", "Feuille de Temps Décembre"

        For I = Range(mois).Rows.Count To 1 Step -1

        If WorksheetFunction.CountBlank(Range(mois).ListObject.ListRows(I).Range) = Range(mois).Columns.Count Then
        .Range(mois).ListObject.ListRows(I).Delete
        End If

        Next I

    End Select
  End With

Next sh

End Sub

Mais avec un fichier exemple, tu obtiendrai une réponse moins hasardeuse...

Cordialement,

Bonjour,

Essaie de compresser ton fichier (en zip) ou utilise le site cijoint.com pour nous joindre ton fichier.

Tu peux aussi l'alléger en supprimant quelques feuilles (pour exemple).

Tu travailles apparemment avec des tableaux (listobjects) et ils ont des propriétés et méthodes particulières.

A te relire.

Cdlt.

Bonjour,

Réécriture partielle :

Sub Supprimer_LignesVides_Toutes_Feuilles()
    Dim sh, mois As String, i As Integer
    For Each sh In Array("Feuille de Temps Janvier", "Feuille de Temps Février", _
     "Feuille de Temps Mars", "Feuille de Temps Avril", "Feuille de Temps Mai", _
     "Feuille de Temps Juin", "Feuille de Temps Juillet", "Feuille de Temps Aout", _
     "Feuille de Temps Septembre", "Feuille de Temps Octobre", _
     "Feuille de Temps Novembre", "Feuille de Temps Décembre")
        mois = Worksheets(sh).Range("G1").Value
        '??
        With Range(mois)
            For i = .Rows.Count To 1 Step -1
                If WorksheetFunction.CountBlank(.Rows(i)) = .Columns.Count Then
                    .Rows(i).EntireRow.Delete
                End If
            Next i
        End With
    Next sh
End Sub

Dans une boucle For Each... Next appliquée à un tableau, la variable de boucle représente un élément du tableau (et non un objet)...

La partie figurant sous les ?? repose sur une interprétation dans la mesure où l'on n'a pas vu le fichier...

Cordialement.

Merci beaucoup à tous les deux !

Effectivement je maîtrise encore très mal les boucles ...

J'ai testé les deux codes du coup et les deux fonctionnent parfaitement !!

Un grand merci !!!

Chloé

Bonjour,

Jean-Eric a écrit :

... ou utilise le site cijoint.com pour nous joindre ton fichier ...

Il y a un problème avec le site cjoint.com , Malwarebytes le bloque ... il y aurait un cheval de Troie.

Voir image en fichier attaché.

Malwarebytes_cjoint.jpg[/attachment]

ric

Bonjour à tous

Je ré ouvre ce post car j'ai des beugs !!! j'espère que c'est la bonne méthode

Suite à vos différents messages, en préparant la version utilisateurs et en supprimant toutes mes saisies test, je me suis rendue compte que si il n'y a aucune saisie sur un mois (sub Supprimer toutes les lignes), la macro affiche une erreur 104 "la methode delete a echouée", il y a t'il un moyen d’empêcher ce beug ?

D'autres part, je voulais avertir l'utilisateur d'un éventuel oubli de saisie et colorer les cellules concernées en jaune mais je rencontre encore un problème "variable objet non définie"

J'ai assemblée plusieurs macro sur une seule pour arriver à mon résultat final "Actualiser le TCD"

Je tiens à préciser que je viens juste de commencer à utiliser VBA et que je ne fais que lire et relire des livres mais que je fais ça toute seule et que personne ne peut m'aider à part vous et que je suis nulle !! Bref, vous allez surement trouver qu'il y a beaucoup d'abération que je crois déceler moi même... !

[code][code][codeSub Actualiser_tcd_recapannuel()
'Macro Renvoie un message pour indiquer que des lignes sont vides
'Actualise le TCD Recap Annuel si aucune ligne vide

Call Deverouiller_Toutes_les_fiches_de_Temps

Call Supprimer_LignesVides_Toutes_Feuilles

Dim sh, mois As String, x As Byte

For Each sh In Array("Feuille de Temps Janvier", "Feuille de Temps Février")

   mois = Worksheets(sh).Range("G1").Value
       Sheets(sh).Select

'Test sur premiere cellule "date jour c11" / Si cellule vide : changement de feuille et exucution de la fonction msbox_vide
saisieexistante = False
If Range("C11").Value <> "" Then saisieexistante = True

If saisieexistante Then

       Range("c11").Select

            Do Until ActiveCell.Value = "Total"
                ActiveCell.Offset(1, 0).Select

            Loop

            x = ActiveCell.Row

            Set plage = Range(Cells(11, 1), Cells(x - 1, 7))

        Dim Cel As Range

'Renvoie un message pour indiquer que des lignes sont vides
vide = False
 For Each Cel In plage
 If Cel = "" Then vide = True

 Next Cel

If vide Then ActiveSheet.Select
If vide Then Cel.Interior.ColorIndex = 19
If vide Then MsgBox ("Merci de compléter les cellules vides sur le mois de ") & mois
If vide Then Exit Sub

End If

Next sh

'Dévérouiller Recap Annuel
Sheets("RECAP_ANNUEL").Select
    ActiveSheet.Unprotect

'Actualiser TCD
Range("E6").Select
    ActiveSheet.PivotTables("Tableau croisé dynamique2").PivotCache.Refresh

'Verouiller recap annuel

Sheets("RECAP_ANNUEL").Select

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFiltering:=True

Call Vérouiller_Fiches_Temps

End Sub

Sub Supprimer_LignesVides_Toutes_Feuilles()

Call Deverouiller_Toutes_les_fiches_de_Temps

    Dim sh, mois As String, i As Integer
    For Each sh In Array("Feuille de Temps Janvier", "Feuille de Temps Février")
        mois = Worksheets(sh).Range("G1").Value

        With Range(mois)
            For i = .Rows.Count To 1 Step -1
                If WorksheetFunction.CountBlank(.Rows(i)) = .Columns.Count Then
                    .Rows(i).EntireRow.Delete
                End If
            Next i
        End With
    Next sh

Call Vérouiller_Fiches_Temps

Sheets("RECAP_ANNUEL").Select

End Sub

Private Function msgbox_vide()

Dim sh, mois As String, x As Byte
   mois = Range("G1").Value

       Range("c11").Select

            Do Until ActiveCell.Value = "Total"
                ActiveCell.Offset(1, 0).Select

            Loop

            x = ActiveCell.Row

            Set plage = Range(Cells(11, 1), Cells(x - 1, 7))

        Dim Cel As Range

'Renvoie un message pour indiquer que des lignes sont vides
vide = False
 For Each Cel In plage
 If Cel = "" Then vide = True

 Next Cel

 If vide Then MsgBox ("Merci de compléter les cellules vides sur le mois de ") & mois

'si cellule vide se rend sur la feuille en question, et revérouille les fiches temps
 ActiveSheet.Select

 If vide Then Call Vérouiller_Fiches_Temps

End Function]

[/code][/code][/code]

Vous verrez que j'ai tenté plusieurs méthodes mais je n'obtiens pas le résultat escompté (en tout cas .. SANS BUG ) !

J'ai joint le fichier par un passe plat, c'est une version test spécial forum

Merci beaucoup à ceux qui auront l'envie et la foi de consacrer un peu de temps à mon usine à gaz !!!

Chloé

Bonjour,

... la macro affiche une erreur 104 "la methode delete a echouée", il y a t'il un moyen d’empêcher ce beug ? ...

Pour ce premier point : mois = ActiveSheet.Range("G1").Value

La macro doit fonctionner sur une feuille "Feuille de temps ....." afin de trouver le mois en G1.

ric

Bonsoir, Salut Ric !

Le problème me paraît un peu plus subtil : ta variable mois correspond à la plage de données d'un tableau Excel, lequel ne peut exister sans avoir au moins une ligne, donc pour ne pas détruire le tableau lors de la suppression, ne l'opérer que jusqu'à la ligne 2, et traiter la ligne 1 à part en testant si la plage comporte encore plus d'une ligne : si c'est le cas on la supprime si elle ne contient rien, sinon on se contente de l'effacer.

Cela devrait éliminer du même coup les erreurs en cas d'absence de données.

Cordialement.

Bonjour,

Pour l'instant, je me suis plutôt préoccupé de ta construction de ta table pour le TCD avec Power Query (que tu as utilisé !...).

C'est un peu tiré par les cheveux.

Si tu avais nommé tes tableaux en les distinguant des autres tableaux, tu pouvais travailler différemment.

Bon, j'ai mis un exemple dans le fichier joint. regarde bien les différentes étapes (comme pour les totaux par exemple).

A te relire.

Cdlt.

Nota 1 : J'ai essayé à partir de ton fichier, mais trop d'erreurs (connexions fantômes, noms masqués, noms avec erreurs, etc...). J'ai de plus des soucis avec Power Query. Ta version 365 Pro Plus est apparemment pas compatible avec ma version 2016 Pro Plus.

Nota 2 : Je regarde demain matin tes procédures VBA pour les tableaux (listobjects) et le reste. Il y a un grand nettoyage à faire.

Bonjour,

Suite…

Ci-dessous ta procédure click_Final revisitée.

A tester.

Cdlt.

Option Explicit
'Proc?dure destin?e ? remplacer Click_Final

Public Sub InsertRowInTable()
Dim lo As ListObject
Dim Cell As Range
Dim i As Long, k As Long
Dim n As Double

    Set lo = ActiveSheet.ListObjects(1)

    If Not lo.InsertRowRange Is Nothing Then Exit Sub

    ActiveSheet.Unprotect

    Application.ScreenUpdating = False

    For i = lo.ListRows.Count To 1 Step -1
        n = WorksheetFunction.CountBlank(lo.ListRows(i).Range)
        If n = lo.ListColumns.Count Then lo.ListRows(i).Delete
    Next i

    lo.DataBodyRange.Interior.Color = xlNone

    For Each Cell In lo.DataBodyRange
        If IsEmpty(Cell) And Len(Cell) = 0 Then
            k = k + 1
            Cell.Interior.Color = 13434879
        End If
    Next Cell

    If k > 0 Then
        MsgBox "Merci de compléter les cellules vides !...", 64, "Information"
        ActiveSheet.Protect
        Exit Sub
    End If

    lo.ListRows.Add

    ActiveSheet.Protect

End Sub

Bonjour Jean-Eric,

Je commence donc avec tes préconisations par la refonte du TCD.

Je remarque que tu n'as pas utilisé Power Query mais un modèle de données.

Je suis navrée mais je me demande vraiment comment tu as réussi à faire ça sans utiliser Query ???

Peux tu m'indiquer comment faire ? ou éventuellement une lecture sur le sujet quelque part sur le net ?

Je regarde tout de suite la suite de ton travail (VBA )

Merci beaucoup pour le temps consacré en tout cas

Chloé

Bonjour,

On a un souci !...

J'ai utilisé Power Query et pas un modèle de données.

Question : Est-ce toi qui a crée les requêtes du classeur que tu as joint ?

C'est le même principe, sinon que je suis parti d'une requête vide.

A te relire.

Cdlt.

Re bonjour Jean-Eric,

Oui c'est bien moi qui ai préparé ces tableaux mais je suis passée par "Combiner les requêtes" ce qui m'a obligé à créer une requête sur chacun des mois.

J'avoue qu'a la première ouverture de ton tableau, n'ayant trouvé aucune feuille sur Visual Basic de requête, je ne me suis pas doutée que tu avais quand même pu effectuer une requête ! et qu'est ce que c'est plus propre comme travail !!

J'ai bien sur essayé de te copier, j'ai donc essayé de créer une requête à partir de ton fichier sur une feuille vierge mais je n'ai pas réussi à combiner les colonnes Date et Durée des mois différents comme tu l'as réalisé par le bouton créer une requete, j'obtiens malheureusement une colonne date, une colonne durée et ainsi de suite relative à chacun des mois.

Sur mon fichier, j'avais procédé de la manière suivante : j'ai ajouté une colonne "attribut" sur chacune des requêtes (sur toutes les requetes de tous les mois) sur la colonne durée afin que les données se combinent en une seule colonne sur mon TCD.

C'était la méthode que j'avais trouvé sur internet;

Et Ma question reste donc entière, comment réaliser ce TCD sans faire une requête pour chacun des mois mais je continue de chercher...

Du coup pour l'instant je n'ai pas encore testé le code VBA car je n'ai pas beaucoup de temps aujourdhui pour me consacrer à ce projet et que cette histoire de requête m'a beaucoup interpellée.

PS : j'ai compris qu'il fallait créer un lien entre le tableau paramétrage "produit" avec chacun des mois, ce que j'ai du faire également dans mon propre fichier.

Merci encore!!

Je me replonge dans mes fouilles Excel !!!

Bonne après midi

Chloé

Re,

Je te prépare les explications pour le fichier que je tai envoyé.

Cdlt.

Bonjour,

Les explications du fichier joint :

Pour commencer, tu vas créer une requête Power Query vide :

Ruban, Données, Récupérer et Transformer, A partir d'autres sources, Requête vide.

Tu es maintenant dans l'éditeur Power Query.

Dans la barre de formule (comme pour Excel) tu saisis :

=Excel.CurrentWorkbook()

Attention ! le langage M est sensible à la casse...

Tu obtiens une table qui comprend 2 colonnes Content et Name.

Les tableaux qui nous intéressent sont ceux dont les noms commencent par Tps_ (dans ton exemple à toi, ce serait donc les tableaux commençant par Feuille de temps).

Tu remarqueras l'intérêt de ce type de nommage des tableaux, puisque l'on va pouvoir effectuer un filtre (comme dans Excel).

On supprime la colonne Name qui n'a pas d'utilité. Comme dans Excel, tu sélectionnes la colonne, clic-droit et Supprimer.

On développe la colonne Content qui contient les tableaux Tps.

Tu sélectionnes la colonne Date et tu filtres en décochant Total qui n'est pas utile.

C'est terminé !...

Ruban, Accueil, Fermer et charger, Fermer et charger dans…

It suffit de créer ton TCD à partir de cette connexion et en fonction de ce que tu veux faire l'ajouter au modèle de données (Power Pivot ?).

A te relire.

Cdlt.

Information ou rappel !

Dans la fenêtre de droite, Paramètres de requête, tu peux naviguer dans les étapes appliquées pour les visualiser, les modifier, etc...

Merci Jean-Eric !!

Je pense que je vais apprendre à faire enfin des TCD rapide sur 12 mois!!

J'ai lu rapidement, malheureusement, je dois me pencher sur un autre sujet plus urgent.

Quand je m'y remet, je te dis si tout fonctionne !!

Mais un enorme merci !!

Bonne journée

Chloé