XL 2010 Mise en forme tableau de longueur variable

[Foulques]

Bonsoir à tous,
Je cherche à faire une macro qui applique une mise en forme pré-définie dans Excel pour les tableaux de mes collègues.
Le souci est que je bute sur les références absolues, alors que les tableaux sont de longueur variables :

ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$P$26"), , xlYes).Name = _

"Tableau1"

ActiveCell.Range("Tableau1[#All]").Select

ActiveSheet.ListObjects("Tableau1").TableStyle = "TableStyleLight21"

Les tableaux commencent toujours en A1, finissent à la colonne P. Il n'y a pas de cellules non vides.
Je dois sûrement définir des variables mais je n'en sais pas plus.

Merci pour votre aide !

 

[TooFatBoy]

Bonjour,

Je crois que c'est doublement inutile de connaître la dernière ligne.

D'une part Excel gère automatiquement le nombre de lignes d'un tableau, donc quand tu utilises une commande, sur une colonne par exemple, elle s'applique à toute la colonne quel que soit le nombre de lignes à ce moment-là.

D'autre part la macro est inutile car quand tu ajoutes une ligne à un tableau, la mise en forme du tableau y est automatiquement appliquée.

 

[Dranreb]

Bonsoir.
Pour isoler un groupe de colonnes non encore mis sous forme de tableau c'est toujours les 5 mêmes solutions :
1) —

Intersect([A1:P1000000], ActiveSheet.UsedRange)

2) —

[A1:P1].Resize([A1000000].End(xlUp).Row)

si la A est rempli sur la dernière ligne
3) —

ColUti([A1:P1])

avec :

Function ColUti(ByVal PlageDép As Range, Optional ByVal LMin As Long, Optional ByVal CMin As Long) As Range
Rem. ——— Plage renseignée de plus qu'une chaîne vide à partir de PlageDép et ce seulement dans ses colonnes dans la UsedRange.
   Set ColUti = PlgUti(PlageDép, Intersect(PlageDép.Worksheet.UsedRange, PlageDép.EntireColumn), LMin, CMin)
   End Function
Function PlgUti(ByVal PlageDép As Range, Optional ByVal PlagExam As Range = Nothing, _
   Optional ByVal LMin As Long, Optional ByVal CMin As Long) As Range
Rem. ——— Plage renseignée de plus qu'une chaîne vide à partir de PlageDép dans PlageExam assumé UsedRange si non précisé.
   Dim LMax As Long, CMax As Long, NbL As Long, NbC As Long
   On Error GoTo RienTrouvé
   If PlagExam Is Nothing Then Set PlagExam = PlageDép.Worksheet.UsedRange
   LMax = PlagExam.Find("*", PlagExam.Cells(1, 1), xlValues, xlWhole, xlByRows, xlPrevious).Row
   CMax = PlagExam.Find("*", PlagExam.Cells(1, 1), xlValues, xlWhole, xlByColumns, xlPrevious).Column
   On Error GoTo 0
   NbL = LMax - PlageDép.Row + 1: If NbL < LMin Then NbL = LMin
   NbC = CMax - PlageDép.Column + 1: If NbC < CMin Then NbC = CMin
   If NbL < 1 Or NbC < 1 Then GoTo CEstToutVide
   Set PlgUti = PlageDép.Resize(NbL, NbC)
   Exit Function
RienTrouvé: Resume CEstToutVide
CEstToutVide: Set PlgUti = Nothing
   End Function

4) —

PlageÀPartirDe([A1])

Si on veut prendre toutes les colonnes renseignées. Avec :

Function PlageÀPartirDe(ByVal CelDéb As Range) As Range
Rem. ——— Plage utilisée à partir de CelDéb.
   Dim NbrLig As Long, NBrCol As Long
   With CelDéb.Worksheet.UsedRange
      NbrLig = .Row + .Rows.Count - CelDéb.Row
      NBrCol = .Column + .Columns.Count - CelDéb.Column
      If NbrLig <= 0 Or NBrCol <= 0 Then Exit Function
      End With
   Set PlageÀPartirDe = CelDéb.Resize(NbrLig, NBrCol)
   End Function

Solution voisine de la 1.
5) —

[A1].CurrentRegion

Ou encore tout simplement :

ActiveSheet.UsedRange

 

[Foulques]

Bonsoir,
Merci pour vos interventions mais il faut que je sois plus précis :

Les tableaux dont je parle sont extraits de notre système central. Il manque une formule de calcul et diverses choses, mais ça c'est bon : malgré que je sois une truffe en VBA j'ai réussi à faire une macro en tâtonnant.
Ce que je cherche à faire c'est finaliser en produisant une jolie présentation, un code qui permet de reproduire un clic sur l'onglet d'accueil, "mise en forme de tableau", et choisir celui correspondant à "TableStyleLight21" .

Seulement, une msgBox demande la confirmation des cellules à mettre en forme en utilisant des références absolues ($).
Comment passer outre cette msgBox et coder que c'est le contenu de ActiveSheet.UsedRange qui est à mettre en forme ?

Merci pour votre aide.

 

[Dranreb]

Ben vous spécifiez ActiveSheet.UsedRange comme second argument du ActiveSheet.ListObjects.Add

À propos: la méthode renvoie un ListObject
Alors vous pouvez fair Dim LOt As ListObject
Set LOt = ActiveSheet.ListObjects.Add(xlSrcRange, ActiveSheet.UsedRange, , xlYes)
Comme ça vous n'avez plus à reconsulter derrière la collection ListObjects, vous pouvez faire :
LOt.TableStyle = "TableStyleLight21"

 

[Dranreb]

Bonjour.
Mais puisque vous parliez de ça :

Ce que je cherche à faire c'est finaliser en produisant une jolie présentation

, je dois vous proposer un produit résultant d'une réflexion personnelle.
Un aspect coloré différent une ligne sur 2, c'est bien, certes, ça peut réduire le risque que le regard change de ligne en se promenant horizontalement, tout en améliorant la présentation. Mais il n'y a pour autant aucune raison qu'une ligne sur 2 soit plus sombre que sa voisine !
Alors j'ai imaginé un style de tableau qu'il est possible de mettre en place, mais pas avec les commandes d'Excel ! Sa procédure StyleTabPerso fait l'objet d'une commande personnalisée sur mon ruban.

 

[Foulques]

Je n'ai pas tout compris mais peu importe puisque ça marche impeccablement !

Merci beaucoup Dranreb !

 

[Dranreb]

Puis-je savoir ce que vous n'avez pas compris ?