TL;DR — Une Table Excel est un
ListObject: une plage qui suit son propre en-tête, son corps de données et ses bords, et qui s'auto-étend quand vous ajoutez des données. Créez-en une avecws.ListObjects.Add(xlSrcRange, range, , xlYes)et nommez-la. Adressez ses parties par leur nom —.HeaderRowRange,.DataBodyRange,.ListColumns("Amount").DataBodyRange— au lieu de calculer des adresses. Ajoutez une ligne aveclo.ListRows.Add, qui met en forme et étend les formules automatiquement. Le seul piège :.DataBodyRangevautNothingsur une table vide (en-tête seul), alors protégez vos boucles avecIf Not lo.DataBodyRange Is Nothing.
Dim lo As ListObject, r As ListRow
Set lo = Worksheets("Data").ListObjects.Add( _
SourceType:=xlSrcRange, _
Source:=Worksheets("Data").Range("A1").CurrentRegion, _
XlListObjectHasHeaders:=xlYes)
lo.Name = "tblSales"
Set r = lo.ListRows.Add ' ajoute une nouvelle ligne en bas
r.Range.Cells(1, 1).Value = "North"
r.Range.Cells(1, 2).Value = 1200
' une colonne de formule se remplit seule, via des references structurees
lo.ListColumns("Total").DataBodyRange.Formula = "=[@Qty]*[@Price]"
La moitié du VBA « réel » tient dans les trois mêmes lignes : trouver la dernière ligne utilisée, écrire
en dessous, étendre les formules. Chacune de ces lignes, c'est vous qui faites une comptabilité que la
feuille pourrait faire elle-même. C'est tout l'intérêt d'une Table Excel : une Table est une plage qui
connaît ses propres bords. Elle suit où finit son en-tête et où commencent ses données, elle grandit
quand vous ajoutez une ligne, et elle garde ses propres colonnes nommées. Convertissez une plage en
ListObject une fois et la chasse à la dernière ligne, le fragile assemblage de chaîne "A2:A" & lastRow,
et le bug « j'ai inséré une ligne et la formule n'a pas suivi » disparaissent tous en même temps. Tout ce
qui suit n'est que cette seule idée appliquée.
Ce que vous allez apprendre
- Le modèle mental — une Table (ListObject) connaît ses propres bords et s'auto-étend
- Convertir une plage en Table avec
ListObjects.Addet la nommer - Adresser les parties par leur nom au lieu de calculer des adresses
- Ajouter et supprimer des lignes de façon fiable avec
ListRows - Le piège du
DataBodyRangequi vautNothingsur une table vide - Les formules à références structurées qui survivent aux insertions, et le parcours des lignes
Le modèle mental : une plage qui connaît ses propres bords
Une plage ordinaire est ignorante de sa propre étendue — Range("A1:D100") fait 100 lignes parce que vous
l'avez dit, et elle reste à 100 lignes quoi que vous ajoutiez. Un ListObject, c'est différent : c'est une
plage dotée de conscience d'elle-même. Elle se souvient que la ligne 1 est un en-tête, que le corps de
données est tout ce qui se trouve en dessous, et que son bord droit est la dernière colonne. Quand vous
écrivez une ligne tout en bas, elle absorbe cette ligne en elle-même et étend sa mise en forme et ses
formules pour correspondre.
Dim lo As ListObject
Set lo = Worksheets("Data").ListObjects("tblSales")
Debug.Print lo.ListRows.Count ' lignes de donnees - pas besoin de End(xlUp)
Debug.Print lo.Range.Address ' toute la table, de l'en-tete a la derniere ligne
Debug.Print lo.DataBodyRange.Address ' juste les donnees, sans en-tete
Parce que l'objet suit ses propres bords, vous ne demandez plus jamais « combien de lignes fait-elle ? » —
vous demandez à la Table. Ce seul changement retire de votre code le rituel End(xlUp), et c'est pourquoi
une Table est la bonne fondation sous un pivot ou un
graphique : ces objets lisent une source qui grandit désormais d'elle-même.
Convertir une plage en Table
Transformez une plage existante en Table avec ListObjects.Add, puis nommez-la pour que votre code comme
vos formules puissent y faire référence clairement :
Dim lo As ListObject
Set lo = Worksheets("Data").ListObjects.Add( _
SourceType:=xlSrcRange, _
Source:=Worksheets("Data").Range("A1").CurrentRegion, _
XlListObjectHasHeaders:=xlYes)
lo.Name = "tblSales" ' desormais referencable comme tblSales partout
CurrentRegion est l'astuce utile pour Source — elle saisit tout le bloc contigu autour de A1, si bien
que vous ne codez pas l'étendue en dur. xlYes indique à Excel que la première ligne contient les en-têtes
(trompez-vous là-dessus et vos noms de colonnes deviennent « Column1 », « Column2 »). Le nom n'est pas
décoratif : tblSales est la façon d'atteindre la Table depuis une autre feuille (Range("tblSales")), la
façon dont les références structurées lisent (tblSales[Amount]), et la façon dont un cache de pivot
pointe dessus. Nommez chaque Table que vous créez en code ; une Table1 sans nom est un nombre magique qui
attend de casser.
Adresser les parties par leur nom
C'est là qu'une Table justifie sa valeur. Chaque partie a une propriété nommée, si bien que vous cessez de
traduire « les montants » en "D2:D" & lastRow :
lo.HeaderRowRange ' les cellules d'en-tete
lo.DataBodyRange ' toutes les lignes de donnees, sans en-tete
lo.ListColumns("Amount").DataBodyRange ' les donnees d'une seule colonne
lo.ListRows(1).Range ' une ligne entiere
lo.Range ' tout, en-tete + corps (+ totaux)
Comparez lo.ListColumns("Amount").DataBodyRange à l'équivalent en plage ordinaire — trouver la colonne
« Amount » en balayant l'en-tête, trouver la dernière ligne, et assembler une chaîne d'adresse. La version
Table continue de fonctionner après que vous avez inséré une colonne, sans rien renommer, et ajouté mille
lignes. Activez une ligne de totaux avec lo.ShowTotals = True et lo.TotalsRowRange apparaît aussi ;
définissez le total d'une colonne avec lo.ListColumns("Amount").TotalsCalculation = xlTotalsCalculationSum.
Ajouter et supprimer des lignes de façon fiable
Pour ajouter une ligne, utilisez ListRows.Add — n'écrivez jamais dans la cellule sous la table en
espérant qu'elle soit absorbée :
Dim r As ListRow
Set r = lo.ListRows.Add ' ajoute en bas, renvoie la nouvelle ligne
r.Range.Cells(1, 1).Value = "North"
r.Range.Cells(1, 2).Value = 1200
' inserer au milieu a la place : lo.ListRows.Add Position:=3
ListRows.Add renvoie le nouveau ListRow, étend la mise en forme et les formules de la Table dessus, et
fait grandir tout pivot ou graphique lié à la Table. Écrire dans la cellule sous la Table avec Cells
est le piège classique : parfois la Table s'auto-étend pour l'avaler, parfois non (cela dépend du réglage
« Inclure dans le tableau » et du fait que la ligne soit vraiment adjacente), si bien que le comportement
est non déterministe d'une machine à l'autre. Supprimez par index de la même façon propre —
lo.ListRows(2).Delete retire une ligne de données et referme le trou, contrairement à un décalage de
cellules à la main.
Le piège de la table vide et le parcours des lignes
Voici le piège du ListObject qui déclenche l'erreur d'exécution 91 : quand une Table n'a que sa ligne
d'en-tête et aucune donnée, .DataBodyRange vaut Nothing, pas une plage vide. Parcourez-la à
l'aveugle et vous plantez :
' SUR - protegez avant de toucher DataBodyRange
If Not lo.DataBodyRange Is Nothing Then
Dim cell As Range
For Each cell In lo.ListColumns("Amount").DataBodyRange
cell.Value = cell.Value * 1.1
Next cell
End If
Protégez tout code qui lit DataBodyRange sur une Table susceptible d'être vide. Pour parcourir les lignes
en tant qu'objets, bouclez sur ListRows — qui est naturellement vide (zéro itération) sur une table
vide, donc n'exige aucune protection :
Dim lr As ListRow
For Each lr In lo.ListRows
Debug.Print lr.Range.Cells(1, 1).Value
Next lr
Pour les formules de colonne, écrivez une seule fois dans toute la colonne avec une référence
structurée et laissez-la se remplir :
lo.ListColumns("Total").DataBodyRange.Formula = "=[@Qty]*[@Price]". La syntaxe [@Qty] signifie « le Qty
de cette ligne », survit aux lignes insérées et aux colonnes réordonnées, et se lit bien mieux que
=B2*C2. Voir VBA Formula pour le comportement des références structurées dans
.Formula, et VBA Range pour les fondamentaux de plage sous une Table.
Comment ExcelMaster aide
Les erreurs de Table qui font perdre du temps sont subtiles : la boucle qui a planté sur une table vide, la
ligne écrite sous la Table qui n'a pas été absorbée, le rapport qui chasse encore la dernière ligne avec
End(xlUp) alors que la Table la connaissait déjà. Aucune n'est une faute de frappe — c'est du code qui
fonctionne mais qui se bat contre une structure censée l'aider.
ExcelMaster traite une Table comme
l'objet qu'elle est. Demandez-lui d'« ajouter les lignes de ce mois et de totaliser la colonne des
montants », et il ajoute avec ListRows.Add, protège DataBodyRange avant de boucler, écrit les formules
de colonne en références structurées qui survivent aux insertions, et adresse les colonnes par leur nom
pour qu'une feuille réordonnée ne casse pas la macro. Vous décrivez le changement des données ; il utilise
les propres bords de la Table au lieu de les recalculer — et chaque pivot et graphique pointant sur cette
Table se met à jour gratuitement.
Questions fréquentes
Comment créer une table (ListObject) en VBA ?
Convertissez une plage avec ListObjects.Add :
Set lo = ws.ListObjects.Add(SourceType:=xlSrcRange, Source:=ws.Range("A1").CurrentRegion, XlListObjectHasHeaders:=xlYes),
puis nommez-la avec lo.Name = "tblSales". CurrentRegion saisit tout le bloc contigu, si bien que vous
ne codez pas l'étendue en dur, et xlYes indique à Excel que la première ligne contient les en-têtes.
Comment ajouter une ligne à une table Excel en VBA ?
Utilisez lo.ListRows.Add, qui ajoute une ligne en bas, la renvoie comme ListRow, et étend la mise en
forme et les formules de la Table : Set r = lo.ListRows.Add: r.Range.Cells(1, 2).Value = 1200. Insérez au
milieu avec lo.ListRows.Add Position:=3. N'écrivez pas dans la cellule sous la Table avec Cells — le
fait qu'elle soit absorbée est non déterministe.
Pourquoi DataBodyRange déclenche-t-il une erreur en VBA ?
Parce qu'une Table qui n'a qu'une ligne d'en-tête et aucune donnée a un DataBodyRange égal à Nothing,
pas une plage vide, si bien que tout code qui y touche déclenche l'erreur d'exécution 91. Protégez-le :
If Not lo.DataBodyRange Is Nothing Then ... End If. Vous pouvez aussi boucler sur lo.ListRows, qui itère
simplement zéro fois sur une table vide et n'exige aucune protection.
Comment référencer une colonne de table en VBA ?
Utilisez ListColumns : lo.ListColumns("Amount").DataBodyRange renvoie les données de cette colonne sans
l'en-tête, et lo.ListColumns("Amount").Range inclut l'en-tête. Dans les formules, utilisez des références
structurées — lo.ListColumns("Total").DataBodyRange.Formula = "=[@Qty]*[@Price]" — où [@Qty] signifie
« le Qty de cette ligne » et survit aux lignes insérées et aux colonnes réordonnées.
Comment cesser d'utiliser End(xlUp) pour trouver la dernière ligne ?
Convertissez la plage en Table. Un ListObject suit ses propres bords, si bien que lo.ListRows.Count
donne le nombre de lignes, lo.DataBodyRange donne les données, et lo.ListRows.Add ajoute sans le
moindre calcul d'adresse. La Table s'auto-étend à mesure que vous ajoutez des lignes, ce qui est exactement
la comptabilité que End(xlUp) faisait à la main.
Testé dans
Testé dans : Excel 365 (Windows 11), VBA 7.1 — vérifié le 02/09/2026.
Guides connexes : VBA Pivot Table · VBA Chart · VBA Range · VBA Formula · VBA AutoFilter