XL 2019 Masquer des lignes avec VBA si supérieures ou inférieures à une date définie

[FloASF63]

Bonjour à tous,
Voici mon tout premier message afin de vous demander de l'aide. Bien que je vous suive très régulièrement et vous m'avez été d'une aide précieuse jusqu'à maintenant. Aujourd'hui je bloque sur quelque chose peut être de tout simple (en tout cas pas pour moi car je n'ai pas de connaissances VBA), à savoir masquer des lignes en fonction d'une date. Je m'explique plus précisément et je joindrais le fichier pour mieux comprendre.

Pour mon activité je dois établir des rapports qui sont destinés aux clients, j'ai donc sur mon fichier excel 2 feuilles, une feuille général et une feuille modèle client. Pour la feuille générale il n'y a rien à toucher, par contre pour la feuille client, j'ai 3 éléments clés à voir savoir deux dates en D3 et F3 et mon tableau de données qui commence ligne 33.
Ce que je souhaiterai dans l'idéal et qui me comblerait , c'est que en gros dans mon tableau il ne soit affiché que les données entre les dates qui sont en D3 et F3 et pour ça j'aurai besoin de simplement masquer les lignes du tableau dont les dates sont <D3 et masquer les lignes >F3 ET effacer le contenu de ces lignes masquées.

Il est possible de la faire en manuel en sélectionnant les lignes à masquer et en effaçant le contenu, mais le problème c'est que je dois faire 500 fiches clients, je souhaiterai donc automatiser cela avec VBA

Je vous remercie grandement par avance pour l'aide que vous m'apporterez.
Florian

 

[Softmama]

Bonsoir,

cette macro dans la feuille Modèle Client répond à la demande, elle se déclenche dès que les dates changent :

Private Sub Worksheet_Change(ByVal Target As Range)
'Si D3 ou F3 est modifiée (Dates de début / fin) alors

If Not Intersect(Target, Range("D3")) Is Nothing Or Not Intersect(Target, Range("F3")) Is Nothing Then
    'On modifie le filtre entre ces 2 dates

    Range("$B$31:$N$171").AutoFilter Field:=1, Criteria1:=">=" & Format(Range("D3"), "00000"), Operator:=xlAnd, Criteria2:="<=" & Format(Range("F3"), "00000")
End If
End Sub

cf. fichier joint.

 

[sylvanu]

Bonjour Floasf, et bienvenu sur XLD, bonjour Softmama,
Juste quelques questions :
- Une fiche correspond elle à un fichier XL ? Ou est ce 500 feuilles dans un même fichier.
- Si on masque des lignes et qu'on les efface. Ne serait ce t il pas plus simple de supprimer ces lignes ?
- Si on a bien 500 fichiers, sont ils tous rangés dans le même dossier ?
- Peux t on envisager un outil qui traite l'ensemble des fiches d'un seul coup?

 

[FloASF63]

Bonsoir,

cette macro dans la feuille Modèle Client répond à la demande, elle se déclenche dès que les dates changent :

Private Sub Worksheet_Change(ByVal Target As Range)
'Si D3 ou F3 est modifiée (Dates de début / fin) alors

If Not Intersect(Target, Range("D3")) Is Nothing Or Not Intersect(Target, Range("F3")) Is Nothing Then
    'On modifie le filtre entre ces 2 dates

    Range("$B$31:$N$171").AutoFilter Field:=1, Criteria1:=">=" & Format(Range("D3"), "00000"), Operator:=xlAnd, Criteria2:="<=" & Format(Range("F3"), "00000")
End If
End Sub

cf. fichier joint.

Bonjour Softmama,

Un grand merci pour votre aide qui répond en effet parfaitement à ce que je recherchais, vous m'enlevez une belle épine du pied
Est ce par hasard il serait possible d'apporter une toute petite modification? Serait-il possible d'ajouter une condition pour que la ligne 32 reste affichée même si la date <D3

Je vous remercie grandement.

 

[FloASF63]

Bonjour Floasf, et bienvenu sur XLD, bonjour Softmama,
Juste quelques questions :
- Une fiche correspond elle à un fichier XL ? Ou est ce 500 feuilles dans un même fichier.
- Si on masque des lignes et qu'on les efface. Ne serait ce t il pas plus simple de supprimer ces lignes ?
- Si on a bien 500 fichiers, sont ils tous rangés dans le même dossier ?
- Peux t on envisager un outil qui traite l'ensemble des fiches d'un seul coup?

Bonjour Sylvanu,
Pour répondre à vos questions
- Ce que j'appelle fiche est en réalité une feuille excel, donc effectivement il y aura 500 feuilles dans un même fichier excel. 500 feuilles clients + 1 feuille générale.
- C'est vrai qu'il aurait été plus simple de supprimer des lignes, mais je ne peux pas, car les calculs de chaque lignes sont incrémentées et conditionnées aux lignes précédentes.
- Tout sera dans un seul fichier excel uniquement.

Je pense que Softmama m'a apporté la solution dont j'avais besoin, en tout cas pour mon utilisation actuelle.

 

[FloASF63]

Softmama,

Je viens de faire le test de votre fichier, un peu plus en profondeur, j'ai 2-3 petites choses encore à vous demander (cela me gêne d'abuser de votre temps).
- Comme mentionné dans mon message précédent, serait-il possible de laisser la ligne 32 affichée
- J'ai remarqué que le tableau et donc le filtre allait de la ligne 31 à 171, comment faire si je souhaite élargir ce tableau (par exemple jusqu'à la ligne 300)
- Enfin les données dans la feuille générale sont ajoutées quotidiennement, chaque jour j'ajoute manuellement une nouvelle ligne, et je remarque que ce n'est pas mis à jour dans la feuille client à moins de supprimer la date et de la remettre ou la le filtre fonctionnera bien en prenant en compte la ou les dernières dates ajoutées. Je voulais donc savoir s'il n'y avait pas un moyen de mettre à jour cela sans avoir à supprimer et remettre la date en D3.

Un grand merci par avance, et encore une fois désolé d'abuser de votre temps.

 

[TooFatBoy]

Bonjour,

Pour mon activité je dois établir des rapports qui sont destinés aux clients, j'ai donc sur mon fichier excel 2 feuilles, une feuille général et une feuille modèle client. Pour la feuille générale il n'y a rien à toucher, par contre pour la feuille client, j'ai 3 éléments clés à voir savoir deux dates en D3 et F3 et mon tableau de données qui commence ligne 33.
Ce que je souhaiterai dans l'idéal et qui me comblerait , c'est que en gros dans mon tableau il ne soit affiché que les données entre les dates qui sont en D3 et F3 et pour ça j'aurai besoin de simplement masquer les lignes du tableau dont les dates sont <D3 et masquer les lignes >F3 ET effacer le contenu de ces lignes masquées.

Ton tableau n'affiche déjà que les données entre ces deux dates, puisque les formules font déjà ce filtrage.

Masquer des lignes et supprimer leur contenu => autant supprimer les lignes.
Autrement dit, autant faire une macro qui va copier uniquement les données en fonction des deux dates.

- C'est vrai qu'il aurait été plus simple de supprimer des lignes, mais je ne peux pas, car les calculs de chaque lignes sont incrémentées et conditionnées aux lignes précédentes.

La feuille "Modèle client" contient effectivement des formules en colonne "Mise " (colonne G) liées aux lignes précédentes mais il est possible d'éventuellement réécrire les formules par VBA.

Il est possible de la faire en manuel en sélectionnant les lignes à masquer et en effaçant le contenu, mais le problème c'est que je dois faire 500 fiches clients, je souhaiterai donc automatiser cela avec VBA

Comment tes 500 fiches sont-elles créées puis remplies ?
Comment les données sont-elles extraites de la feuille "Général" pour un client ??? Y a-t-il quelque chose pour différencier les achats/ventes selon le client ?

 

[Softmama]

Bonsoir,

Une solution en PJ,
J'ai viré les formules qui filtraient et ai laissé un vrai filtre.
(pas compris à quoi sert la formule compliquée en colonne G.)

Au final, ça doit répondre à votre demande :

Private Sub Worksheet_Activate() 'à l'activation de la feuille
Dim derLigne As Long, a
 'On récupère la taille du tableau de base
 derLigne = Split(Feuil1.Range("B2").CurrentRegion.Address, "$")(4)
 With Feuil3
  .Range("B35:L" & .Range("B35").End(xlDown).Row).ClearContents 'On efface le tableau dans la feuille Modèle Client
  ' et on remet un nouveau tableau :
  .Range("B35:B" & derLigne + 33).FormulaR1C1 = "=Général!R[-33]C"
  .Range("D35:I" & derLigne + 33).FormulaR1C1 = "=Général!R[-33]C[-1]"
  .Range("J35:J" & derLigne + 33).FormulaR1C1 = "=IF(AND(R53C13<=Général!R[-32]C2,R54C13>=Général!R[-32]C2,Général!R[-32]C[-1]=""G""),RC[-3]*0.81,IF(RC[-3]="""","""",-RC[-3]))"
 'On efface/ remet le filtre en remettant la date en D3
  a = .Range("D3")
  .Range("D3") = ""
  .Range("D3") = a
 End With
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("D3")) Is Nothing Or Not Intersect(Target, Range("F3")) Is Nothing Then
    If Range("D3") <> "" Then Range("$B$34").AutoFilter Field:=1, Criteria1:=">=" & Format(Range("D3"), "00000"), Operator:=xlAnd, Criteria2:="<=" & Format(Range("F3"), "00000")
  End If
End Sub

Bonne soirée,

 

[FloASF63]

Bonjour,



Ton tableau n'affiche déjà que les données entre ces deux dates, puisque les formules font déjà ce filtrage.

Masquer des lignes et supprimer leur contenu => autant supprimer les lignes.
Autrement dit, autant faire une macro qui va copier uniquement les données en fonction des deux dates.

La feuille "Modèle client" contient effectivement des formules en colonne "Mise " (colonne G) liées aux lignes précédentes mais il est possible d'éventuellement réécrire les formules par VBA.



Comment tes 500 fiches sont-elles créées puis remplies ?
Comment les données sont-elles extraites de la feuille "Général" pour un client ??? Y a-t-il quelque chose pour différencier les achats/ventes selon le client ?

Bonsoir Marcel et merci d'avoir consacré un peu de temps à ma cause

Alors pour répondre à tes questions, mes feuilles clients sont créées manuellement en dupliquant la feuille modèle. Dès qu'il y a un nouveau client, je fais clic droit sur la feuille modèle client puis déplacer ou copier.
Concernant le remplissage de la feuille client, hormis les informations de type noms et date du jour que je remplie manuellement, mon tableau débutant ligne 31 est automatisé et en lien avec la feuille générale. Concrètement je n'ai plus qu'a remplir quotidiennement la feuille générale pour que les données pour le client soient transposées sur la feuille client.
Pour le client en cellule N50, il y a un %, qui permet de différencier les informations que verra le client.

 

[TooFatBoy]

mes feuilles clients sont créées manuellement en dupliquant la feuille modèle. Dès qu'il y a un nouveau client, je fais clic droit sur la feuille modèle client puis déplacer ou copier.

OK. C'est parfaitement clair.

Concernant le remplissage de la feuille client, hormis les informations de type noms et date du jour que je remplie manuellement, mon tableau débutant ligne 31 est automatisé et en lien avec la feuille générale.
Concrètement je n'ai plus qu'a remplir quotidiennement la feuille générale pour que les données pour le client soient transposées sur la feuille client.

Oui, et donc le filtrage est déjà effectué par les formules. Donc la question de départ n'a pas de raison d'être, me semble-t-il.

Pour le client en cellule N50, il y a un %, qui permet de différencier les informations que verra le client.

Perso, je trouve bizarre de mettre ça en plein milieu du tableau, lignes 50, 51 et 52.
J'aurais plutôt mis ça sur des lignes qui ne bougent pas, lignes 27, 29 et 30.
Mais ça n'engage que moi.

 

[FloASF63]

Bonsoir,

Une solution en PJ,
J'ai viré les formules qui filtraient et ai laissé un vrai filtre.
(pas compris à quoi sert la formule compliquée en colonne G.)

Au final, ça doit répondre à votre demande :

Private Sub Worksheet_Activate() 'à l'activation de la feuille
Dim derLigne As Long, a
'On récupère la taille du tableau de base
derLigne = Split(Feuil1.Range("B2").CurrentRegion.Address, "$")(4)
With Feuil3
  .Range("B35").CurrentRegion.Offset(1).ClearContents 'On efface le tableau dans la feuille Modèle Client
  ' et on remet un nouveau tableau :
  .Range("B35:B" & derLigne + 33).FormulaR1C1 = "=Général!R[-33]C"
  .Range("D35:I" & derLigne + 33).FormulaR1C1 = "=Général!R[-33]C[-1]"
  'On efface/ remet le filtre en remettant la date en D3
  a = .Range("D3")
  .Range("D3") = ""
  .Range("D3") = a
End With
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("D3")) Is Nothing Or Not Intersect(Target, Range("F3")) Is Nothing Then
    If Range("D3") <> "" Then Range("$B$34").AutoFilter Field:=1, Criteria1:=">=" & Format(Range("D3"), "00000"), Operator:=xlAnd, Criteria2:="<=" & Format(Range("F3"), "00000")
End If
End Sub

Bonne soirée,

Bonsoir Softmama,

En fait la formule en colonne G, est pour le client , car il ne doit pas avoir les mêmes chiffres (en Colonne G et J) que dans la feuille générale. Ces deux colonnes sont propres à chaque client en fonction de leur profil déterminé en Cellule N52.
C'est la ou réside toute la complexité de mon problème car je ne peux pas simplement "copier-coller" les données du général dans la feuille client.
Votre première solution d'hier est vraiment tout proche de ce que je recherche, seulement dans votre première solution , lorsque j'ajoute une nouvelle donnée dans le général, celle ci n'est pas "mise à jour" dans la feuille client, pour que ce soit mis à jour je dois supprimer la date en D3 et la remettre.

 

[FloASF63]

OK. C'est parfaitement clair.



Oui, et donc le filtrage est déjà effectué par les formules. Donc la question de départ n'a pas de raison d'être, me semble-t-il.



Perso, je trouve bizarre de mettre ça en plein milieu du tableau, lignes 50, 51 et 52.
J'aurais plutôt mis ça sur des lignes qui ne bougent pas, lignes 27, 29 et 30.
Mais ça n'engage que moi.

Il est vrai que c'est bizarre de mettre ça en plein milieu, je l'ai laissé pour visualiser, pour le client je masquerai cette information en mettant simplement le texte en blanc. Le client recevra cette feuille non pas en excel mais en PDF.

 

[Softmama]

Bonsoir,

Bien, du coup j'ai rajouté la formule indigeste de la colonne G :

Private Sub Worksheet_Activate() 'à l'activation de la feuille
Dim derLigne As Long, a
 'On récupère la taille du tableau de base
 derLigne = Split(Feuil1.Range("B2").CurrentRegion.Address, "$")(4)
 With Feuil3
  .Range("B35:L" & .Range("B35").End(xlDown).Row).ClearContents 'On efface le tableau dans la feuille Modèle Client
  ' et on remet un nouveau tableau :
  .Range("B35:B" & derLigne + 33).FormulaR1C1 = "=Général!R[-33]C"
  .Range("D35:I" & derLigne + 33).FormulaR1C1 = "=Général!R[-33]C[-1]"
  .Range("J35:J" & derLigne + 33).FormulaR1C1 = "=IF(AND(R53C13<=Général!R[-32]C2,R54C13>=Général!R[-32]C2,Général!R[-32]C[-1]=""G""),RC[-3]*0.81,IF(RC[-3]="""","""",-RC[-3]))"
  .Range("G35:G" & derLigne + 33).FormulaR1C1 = "=IF(ISBLANK(RC[-1]),"""",IF(AND(R52C14=Général!R3C13),ROUND(R32C10/Général!R3C14,0),IF(AND(R52C14=Général!R4C13),ROUND(R32C10/Général!R4C14,0),IF(AND(R52C14=Général!R5C13),ROUND(R32C10/Général!R5C14,0),IF(AND(R52C14=Général!R6C13),ROUND(R32C10/Général!R6C14,0),IF(AND(R52C14=Général!R7C13),ROUND(R32C10/Général!R7C14,0),IF(AND(R52C14=Général!R8C13),ROUND(R32C10/Généra" & _
        "l!R8C14,0),IF(AND(R52C14=Général!R9C13),ROUND(R32C10/Général!R9C14,0),IF(AND(R52C14=Général!R10C13),ROUND(R32C10/Général!R10C14,0),IF(AND(R52C14=Général!R11C13),ROUND(R32C10/Général!R11C14,0),IF(AND(R52C14=Général!R12C13),ROUND(R32C10/Général!R12C14,0))))))))))))" 'On efface/ remet le filtre en remettant la date en D3
  a = .Range("D3")
  .Range("D3") = ""
  .Range("D3") = a
 End With
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("D3")) Is Nothing Or Not Intersect(Target, Range("F3")) Is Nothing Then
    If Range("D3") <> "" Then Range("$B$34").AutoFilter Field:=1, Criteria1:=">=" & Format(Range("D3"), "00000"), Operator:=xlAnd, Criteria2:="<=" & Format(Range("F3"), "00000")
  End If
End Sub

et là, je pense que c'est ok.

 

[Softmama]

Je me suis penché sur la formule de la colonne G, que j'ai simplifiée...

Pour le reste, c'est pareil :

 

[TooFatBoy]

@FloASF63 : en colonne G, tu peux effectivement largement simplifier la formule.

Perso, j'ai trouvé :

=SI(OU(F33="EUR/USD";F33="AUD/USD";F33="GBP/USD");ARRONDI(SOMME($J$32:J32)/RECHERCHEV($N$24;Général!M3:N12;2;FAUX);0);"")

à mettre en G32 et à tirer vers le bas.


@Softmama : je crois que tu as oublié la somme.