Macro inserer ligne, supprimer ligne en fonction taille tcd

S

sim

XLDnaute Occasionnel
Bonjour a toutes et a tous!!!


Ce week end, j'ai essayer de decouvrir comment mettre en page une feuille en constante evolution. Et je crois avoir trouver la logique mais je n'arrive pas a la mettre en place....

Sur une feuille, j'ai 4 TCD disposer en dessous les un de autres. Ces TCD evoluent en fonction d'un seul critere: le marche

Evidemment, pour chaques marche le nombre d'information est different et du coup le nombre de ligne aussi.

Jusqu'a present j'ai laisser asses d'espace entre chaque tcd pour etre sur que un tcd n'empiete pas sur celui qui est en dessous.

Ce que j'aimerai c'est qu'il y est le meme espace entre chaque TCD peut importe le marche et le nombre de ligne des TCD.

Je pensais donc faire une macro qui ferai les choses suivantes:

Definir une variable pour mes TCD.
Definir par un nom la cellule ou est inscrit le titre du TCD (ex dans mon ex: affiliate selling to the market distributor)
Grace a cela la macro pourrait reconnaitre la position ( la ligne) de mes titres.
J'arrive a obtenir cette position par la formule ligne() ou en anglais pour moi, row()

Donc on resume a ce moment la j'ai
Definit mon TCD
Je sais ou il commence

Maintenant il faut calculer la taille du TCD....j'ai reussi a denicher ce petit bout de code me permettant de calculer la taille de mon tcd en entier avec la fonction TableRange2.Rows.Count

Voici le code:

Code: 
Sub compterNombreLignesTCD()
    Dim Pvt As PivotTable

    'Définit le TCD    
    Set Pvt = Worksheets("Feuil1").PivotTables("Tableau croisé dynamique1")

    'TableRange2 :
    'plage contenant l'intégralité du rapport de tableau croisé dynamique,
    'y compris les champs de page.
    MsgBox Pvt.TableRange2.Rows.Count
End Sub


Donc on sait ou il commence combien il mesure.....

A chaque changement de marche les tcd sont mis a jour (normal pour avoir la bonne information!!!)

Donc je pensais inserer un nombre de ligne important avant le changement
et ensuite supprimer les ligne en trop.

Ajouter des ligne pour ensuite les supprimer donne asses de place au tcd pour ne pas "ecraser" celui du dessous....


Alors voila la logique a laquelle je suis parvenu...mais je bloque tres sincerement pour la mettre en route.

Dans mon exemple, vous pouvez changer le marche en apuyant sur le bouton "Market" en haut a gauche de la feuille "Current_market".

Ensuite, je vous laisse le soin de parcourir les codes VBA, si jamais vous avez besoin de voir comment tout ca marche.....

Merci d'avance a ceux qui pourront m'aider...parce que la je me sens un peu seul face au souci. :rolleyes::confused:

Au plaisir de vous lire...:D

Sim
 

Pièces jointes

  • macro mise en page.zip
    228.9 KB · Affichages: 138
Trier par date Trier par votes
chris

chris

XLDnaute Barbatruc
Re : Macro inserer ligne, supprimer ligne en fonction taille tcd

Re

Les TCD étant collés, j'avais point vu le second à droite.

Il faut donc calculer lig2a et lig2b l'un par rapport à B58, l'autre par rapport à E58 et prendre le plus grand des 2.

Sinon pour l'explication : on part de la ligne 58 (juste au dessus du 2ème groupe de TCD et on cherche la dernière cellule vide en remontant, ce qui donne donc la fin du TCD

Code: 
    lig1a = Range("B58").End(xlUp).Row + 2
    lig1b = Range("E58").End(xlUp).Row + 2
    lig1 = Application.Max(lig1a, lig1b)
    Range("B" & lig1 & ":B58").EntireRow.Hidden = True
 
Dernière édition:
Vote positif 0 Vote négatif
S

sim

XLDnaute Occasionnel
Re : Macro inserer ligne, supprimer ligne en fonction taille tcd

Hummmmm.....je sens qu'on va y arriver, merci bcp :D


Je viens de faire ca pour voir si ca marchais, et j'ai l'impression que ca ne prend en compte que la deuxieme partie du code

Code: 
    lig1a = Range("B54").End(xlUp).Row + 2
    lig1b = Range("E54").End(xlUp).Row + 2
    lig1 = Application.Max(lig1a, lig1b)
    Range("B" & lig1 & ":B54").EntireRow.Hidden = True
    
    ActiveSheet.Rows.EntireRow.Hidden = False
    lig1 = Range("B101").End(xlUp).Row + 1
    Range("B" & lig1 & ":B101").EntireRow.Hidden = True

Et je voudrais savoir si c'est possible que l'on cache toutes les lignes sauf celle avant la derniere cellule pleine..( tout ca pourque on continue a voir le cadre du tcd)

J'ai aussi change les limite de facon a laisser 3 ou 4 lignes entre chaqe tcd....

Merci
 
Vote positif 0 Vote négatif
S

sim

XLDnaute Occasionnel
Re : Macro inserer ligne, supprimer ligne en fonction taille tcd

Je viens de voir que

Code: 
Row + 2

cela permet de mne pas avoir le cadre de mon tcd rogner a cause de la ligne cachee, en fait ca caque celle du dessous....tu avais donc anticipe ma demande.....!!!!

Et j'ai aussi realiser que repeter le

Code: 
ActiveSheet.Rows.EntireRow.Hidden = False

faisait que les ligne que je venais de cacher juste au dessus reapparaissait!! normal!!!

je vais essayer de faire un test grandeur nature!!! je reviens pour te dire si cela a marche
 
Vote positif 0 Vote négatif
S

sim

XLDnaute Occasionnel
Re : Macro inserer ligne, supprimer ligne en fonction taille tcd

Alors ca marche nikel, mais je sais pas pourquoi mes tcd ne sont pas rogner sauf pour le l'avant dernier

je remt le code, si jamais c'est juste un petit truc a changer pour que, les lignes soit cachees sauf la premiere en dessous du tcd

ActiveSheet.Rows.EntireRow.Hidden = False

lig1a = Range("B54").End(xlUp).Row + 2
lig1b = Range("E54").End(xlUp).Row + 2
lig1 = Application.Max(lig1a, lig1b)
Range("B" & lig1 & ":B54").EntireRow.Hidden = True

lig2 = Range("B101").End(xlUp).Row + 2
Range("B" & lig2 & ":B101").EntireRow.Hidden = True

lig3 = Range("B161").End(xlUp).Row + 2
Range("B" & lig3 & ":B161").EntireRow.Hidden = True
Cliquez pour agrandir...

Merci d'avance pour ce petit detail.... tu es mon sauveur sur ce coup la!!

J'en etait sur 3 malheureuses lignes de code est mon probleme est resolu!!!!

Sim
 
Vote positif 0 Vote négatif
S

sim

XLDnaute Occasionnel
Re : Macro inserer ligne, supprimer ligne en fonction taille tcd

J'ai trouver dans l'avant dernier TCD parfois la derniere cellle remplie dans la colone B ne correspond pas a la derniere ligne du tcd

j'ai donc changer pour

Code: 
    lig3 = Range("F161").End(xlUp).Row + 2
    Range("F" & lig3 & ":F161").EntireRow.Hidden = True


La colone F etant toujour remplie jusqu'a la fin du TCD


Donc nikel.....

Bon j'ai une autre question du coup, mais pas en lien directement, c'est l'impression

j'ai une boucle qui me genere tout les marche sant pour autant creeer les feuille et aui me les imprime....est ce que les lignes cachees reste cachees a l'impression nous allons voir je fais le test et je reviens

En tout cas merci!!!

Sim
 
Vote positif 0 Vote négatif
chris

chris

XLDnaute Barbatruc
Re : Macro inserer ligne, supprimer ligne en fonction taille tcd

Bonjour

En fait il faut lig2a et lig2b pour le second groupe avec lig2 pour le max, puis lig3a lig3b pour le 3ème avec lig3, etc de façon à toujours tenir compte du plus long des 2 si tu en as 2 par groupe.

On peut réutiliser les mêmes noms de variables d'un groupe à l'autre mais c'est pour plus de clarté.

Le principal c'est que cela marche !
 
Vote positif 0 Vote négatif
S

sim

XLDnaute Occasionnel
Re : Macro inserer ligne, supprimer ligne en fonction taille tcd

Ok je vais le faire par anticipation,

normalement on a 2 TCD que dans le premier groupe mais apres 1 TCD par groupe, mais on sait jamais ce que nous
reserve l'avenir donc vaut mieux etre prudent.

En tout cas merci.

Une petite curiosite tout de meme!! :p

Si aulieu de cacher nous avions supprimer ca aurait ete pareil ou tres similaire comme code non??

Sim
 
Vote positif 0 Vote négatif
S

sim

XLDnaute Occasionnel
Re : Macro inserer ligne, supprimer ligne en fonction taille tcd

Une derniere question tout de meme!!!!!!

J'ai integrer ce code dans un code evenementielle et malheureusement ca me fait bcp ralentir le changement de marche

Quelqu'un aurait il une solution pour que cela ne se produise pas....avant d'avoir rajouter ce code le changement de marche ne posait aucun probleme en terme de temps...

Voici le code dans le module de feuille Current_market

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    If Target.Address = "$D$7" Then
        On Error Resume Next
        ActiveSheet.PivotTables("affiliate").PivotFields("Market"). _
            CurrentPage = Target.Value
        ActiveSheet.PivotTables("product").PivotFields("Market"). _
            CurrentPage = Target.Value
        ActiveSheet.PivotTables("flows").PivotFields("Market"). _
            CurrentPage = Target.Value
        ActiveSheet.PivotTables("brand").PivotFields("Market"). _
            CurrentPage = Target.Value
        ActiveSheet.PivotTables("royalty").PivotFields("Market"). _
            CurrentPage = Target.Value
        On Error GoTo 0
    End If
    
    ActiveSheet.Rows.EntireRow.Hidden = False
    
    lig1a = Range("B54").End(xlUp).Row + 2
    lig1b = Range("E54").End(xlUp).Row + 2
    lig1 = Application.Max(lig1a, lig1b)
    Range("B" & lig1 & ":B54").EntireRow.Hidden = True
    
    lig2 = Range("B101").End(xlUp).Row + 2
    Range("B" & lig2 & ":B101").EntireRow.Hidden = True
    
    lig3 = Range("F161").End(xlUp).Row + 2
    Range("F" & lig3 & ":F161").EntireRow.Hidden = True

End Sub


Peut etre faut il le mettre dans un module standard et pas un module de feuille mais a ce moment la je peux pas utiliser la fonction

worksheet_change, si j'ai bien tout compris.....du comment dois je proceder??

Merci d'avance

Sim
 
Vote positif 0 Vote négatif
Pierrot93

Pierrot93

XLDnaute Barbatruc
Re : Macro inserer ligne, supprimer ligne en fonction taille tcd

Re,

essaye ceci :
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

    Application.ScreenUpdating = False
    If Target.Address = "$D$7" Then
        On Error Resume Next
        ActiveSheet.PivotTables("affiliate").PivotFields("Market"). _
            CurrentPage = Target.Value
        ActiveSheet.PivotTables("product").PivotFields("Market"). _
            CurrentPage = Target.Value
        ActiveSheet.PivotTables("flows").PivotFields("Market"). _
            CurrentPage = Target.Value
        ActiveSheet.PivotTables("brand").PivotFields("Market"). _
            CurrentPage = Target.Value
        ActiveSheet.PivotTables("royalty").PivotFields("Market"). _
            CurrentPage = Target.Value
        On Error GoTo 0
    End If
    
    ActiveSheet.Rows.EntireRow.Hidden = False
    
    lig1a = Range("B54").End(xlUp).Row + 2
    lig1b = Range("E54").End(xlUp).Row + 2
    lig1 = Application.Max(lig1a, lig1b)
    Range("B" & lig1 & ":B54").EntireRow.Hidden = True
    
    lig2 = Range("B101").End(xlUp).Row + 2
    Range("B" & lig2 & ":B101").EntireRow.Hidden = True
    
    lig3 = Range("F161").End(xlUp).Row + 2
    Range("F" & lig3 & ":F161").EntireRow.Hidden = True

Application.EnableEvents = True
End Sub
 
Vote positif 0 Vote négatif
chris

chris

XLDnaute Barbatruc
Re : Macro inserer ligne, supprimer ligne en fonction taille tcd

Bonjour
Salut Pierrot

Le masquage de lignes provoque un recalcul : tu peux aussi ajouter
Code: 
Application.Calculation = xlCalculationManual
avant la ligne qui affiche tout
et
Code: 
Application.Calculation=xlCalculationAutomatic
après les divers masquages
 
Vote positif 0 Vote négatif
S

sim

XLDnaute Occasionnel
Re : Macro inserer ligne, supprimer ligne en fonction taille tcd

Le forum, Pierrot, Chris

Pierrot ton code marche nikel....encore une fois merci

Chris......lol je suis desole mais meme si le code de pierrot marche parfqitement j'aimerai bien
que vous m'aidiez a comprendre, et aussi a placer ces codes car

avant la ligne qui affiche tout
après les divers masquages

cq me parle pas trop.....s'il vous plait :)

Merci en tout cas a tout les deux!!!

Sim

Edit:

je demande ca pour deux raison

la premiere, au cours de mes divers recherche j'ai vu que utiliser enable_event = False, est dangereux......

La deuxieme je suis curieux!!!!

Merci ;)
 
Dernière édition:
Vote positif 0 Vote négatif
Pierrot93

Pierrot93

XLDnaute Barbatruc
Re : Macro inserer ligne, supprimer ligne en fonction taille tcd

Re, bonsoir Chris:),
A noter également que la modification d'un champ du tcd va déclencher l'événement change de la feuille...

je demande ca pour deux raison
la premiere, au cours de mes divers recherche j'ai vu que utiliser enable_event = False, est dangereux......
Cliquez pour agrandir...
c'est dangereux est un bien grang mot.... en cas de plantage les procédures événementielles sont désactivées, tu peux les relancer soit par le code soit lorsqu'excel est réouvert...
j'ai rajouté une gestion d'erreur pour éviter ce problème....

Code: 
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo fin
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    If Target.Address = "$D$7" Then
        On Error Resume Next
        ActiveSheet.PivotTables("affiliate").PivotFields("Market"). _
            CurrentPage = Target.Value
        ActiveSheet.PivotTables("product").PivotFields("Market"). _
            CurrentPage = Target.Value
        ActiveSheet.PivotTables("flows").PivotFields("Market"). _
            CurrentPage = Target.Value
        ActiveSheet.PivotTables("brand").PivotFields("Market"). _
            CurrentPage = Target.Value
        ActiveSheet.PivotTables("royalty").PivotFields("Market"). _
            CurrentPage = Target.Value
        On Error GoTo 0
    End If
    
    ActiveSheet.Rows.EntireRow.Hidden = False
    
    lig1a = Range("B54").End(xlUp).Row + 2
    lig1b = Range("E54").End(xlUp).Row + 2
    lig1 = Application.Max(lig1a, lig1b)
    Range("B" & lig1 & ":B54").EntireRow.Hidden = True
    
    lig2 = Range("B101").End(xlUp).Row + 2
    Range("B" & lig2 & ":B101").EntireRow.Hidden = True
    
    lig3 = Range("F161").End(xlUp).Row + 2
    Range("F" & lig3 & ":F161").EntireRow.Hidden = True
Exit Sub
fin:
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
MsgBox "attention erreur.." & vbCrLf & Err.Description
End Sub
 
Vote positif 0 Vote négatif
S

sim

XLDnaute Occasionnel
Re : Macro inserer ligne, supprimer ligne en fonction taille tcd

Re,

cela beug me disant que

Code: 
lig1a

n'est pqs definit.......bizzard non

haaa!!!! et qu'est ce que option explicit veut dire, ou plutot a quoi ca sert??

Merci
 
Vote positif 0 Vote négatif

Discussions similaires

C
  • Résolu(e)
XL 2016 Problème de TCD avec Champ Date
Réponses
11
Affichages
329
chris
chris
L
  • Question
Microsoft 365 Comparer 2 TCD en créant un 3 ème
Réponses
4
Affichages
125
lilie91
L
A
  • Question
XL 2019 Convertir un tableau calculé en TCD
2
Réponses
15
Affichages
536
nat54
nat54
G
  • Question
XL 2016 Lier un TCD et un tableau Excel
Réponses
7
Affichages
241
OKBI
O
V
  • Question
XL 2016 Durée entre deux horaires de cellules non vides en TCD
Réponses
2
Affichages
160
Vincent C
V
F
  • Question
Microsoft 365 macro edition détail d'un TCD et envoi dans un email
Réponses
1
Affichages
98
fabancel
F
looky62
  • Question
Microsoft 365 PB Concaténation de ref en une phrase
Réponses
3
Affichages
128
looky62
looky62
G
  • Question
XL 2010 Copier coller une image dans le corps de message via gmail
Réponses
8
Affichages
387
gwenn49
G
3
  • Question
XL 2010 Copier un tableau croisé dynamique même si celui ci a un nombre de ligne variable
Réponses
4
Affichages
294
306255
3
S
  • Question
Microsoft 365 Création d'un TCD avec 2 tableaux ayant les mêmes entêtes de colonne et les même données
Réponses
9
Affichages
268
STEPHANIE1975
S

Membres actuellement en ligne

2 membres et 48 invités.

Partager cette page

Tutos vidéos

Power Query : Ajouter des requêtes (Excel et Power BI)
Créez votre première fonction LAMBDA dans Excel en 3 étapes!
LAMBDA, l’arme secrète de Microsoft pour Excel !
Office Insider - Découvrez les nouveautés Excel avant tout le monde

Statistiques des forums

Discussions
312 206
Messages
2 086 207
Membres
103 157
dernier inscrit
youma