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 à 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é