VBA UsedRange contre CurrentRegion dans Excel — saisir tout le bloc de données (et à laquelle se fier)

TL;DR — Les deux saisissent « tout le rectangle », mais répondent à des questions différentes. Worksheet.UsedRange, c'est tout ce qu'Excel a un jour touché — une boîte englobante mise en cache qui inclut les cellules au formatage seul et ne rétrécit pas quand vous supprimez des données, donc elle surestime. Range.CurrentRegion est le bloc contigu autour d'une cellule, calculé en direct (ce que sélectionne Ctrl+Maj+*), et il s'arrête à la première ligne ou colonne entièrement vide. Pour un seul tableau propre, Range("A1").CurrentRegion est le choix fiable. Réservez UsedRange au « efface toute la feuille », et ne lisez jamais UsedRange.Rows.Count comme s'il s'agissait du numéro de la dernière ligne.

Une fois que vous savez trouver la dernière ligne, la question suivante est en général « donne-moi le bloc entier d'un coup pour que je puisse boucler, copier ou le mettre en forme ». VBA possède deux fonctions intégrées qui semblent interchangeables et ne le sont pas. Se tromper de choix est la source de deux des bugs de macro les plus courants : traiter des centaines de lignes fantômes, et lire un nombre de lignes comme s'il s'agissait d'un numéro de ligne.

Ce que vous allez apprendre

Le modèle mental : une boîte englobante mise en cache contre un bloc contigu calculé en direct
Pourquoi UsedRange surestime et refuse de rétrécir
Pourquoi UsedRange peut ne pas commencer en A1 — le bug du décalage de comptage
Pourquoi CurrentRegion s'arrête aux lignes vides, et quand c'est exactement ce que vous voulez
Une règle claire pour choisir laquelle selon la tâche

Le modèle mental : un instantané périmé contre une mesure en direct

UsedRange est un instantané qu'Excel garde pour lui. C'est le plus petit rectangle ayant un jour englobé quoi que ce soit que vous ayez fait sur la feuille — saisir une valeur, appliquer une bordure, poser un remplissage. Excel le met en cache et l'actualise paresseusement : il est donc en retard sur la réalité, grandissant avec empressement et rétrécissant à contrecœur.

CurrentRegion est une mesure en direct prise là où vous vous trouvez. Donnez-lui une cellule et elle s'étend vers l'extérieur — haut, bas, gauche, droite — jusqu'à rencontrer une ligne ou une colonne entièrement vide de chaque côté. C'est la version code du clic sur une cellule suivi de Ctrl+Maj+* (ou Ctrl+A une fois). Rien n'est mis en cache ; c'est calculé à l'instant où vous le demandez.

Sub GrabTheBlock()
    Dim ws As Worksheet
    Set ws = ActiveSheet

    Debug.Print ws.UsedRange.Address              ' boîte englobante mise en cache de la feuille
    Debug.Print ws.Range("A1").CurrentRegion.Address  ' bloc en direct autour de A1
End Sub

Cette différence — en cache et collant contre en direct et local — explique tous les pièges qui suivent.

La règle qui évite le bug des lignes fantômes : UsedRange surestime

Le mode de défaillance qui fait perdre le plus de temps : vous bouclez sur UsedRange et traitez bien plus de lignes que vous n'avez de données, parce que UsedRange compte les cellules qui ne portent qu'un formatage. Mettez une bordure sur A1000 une seule fois, supprimez la valeur, et A1000 vit toujours dans UsedRange. Pire, supprimer le contenu des lignes ne fait pas rétrécir UsedRange — Excel conserve l'ancienne boîte englobante jusqu'à l'enregistrement du classeur ou une réinitialisation forcée.

' UsedRange peut être bien plus grand que vos données réelles
Dim r As Range
For Each r In ws.UsedRange.Rows
    ' ...ceci peut parcourir des centaines de lignes vides, au formatage seul
Next r

Si vous devez utiliser UsedRange après de lourdes modifications, forcez Excel à le recalculer en le lisant une fois, ou réinitialisez-le explicitement :

Dim junk As Range
Set junk = ws.UsedRange          ' le toucher provoque un rafraîchissement

La leçon plus profonde : UsedRange répond à « qu'est-ce qu'Excel a touché ? », pas à « où sont mes données ? ». Ce sont des questions différentes, et la réponse ne coïncide que parfois.

La règle des nombres contre les numéros : UsedRange peut ne pas commencer en A1

Le deuxième bug classique. Si vos données commencent en C5, alors UsedRange commence lui aussi en C5 — pas en A1. Son .Rows.Count est donc la hauteur du bloc, pas le numéro de la dernière ligne, et son .Cells(1, 1) est C5, pas A1.

' Les données vivent dans C5:F20
ws.UsedRange.Rows.Count          ' -> 16   (un NOMBRE : lignes 5 à 20)
ws.UsedRange.Row                 ' -> 5    (là où il commence)
' numéro de dernière ligne = première ligne + nombre - 1
ws.UsedRange.Row + ws.UsedRange.Rows.Count - 1   ' -> 20  (la vraie dernière ligne)

Lire UsedRange.Rows.Count comme « la dernière ligne » n'est correct que lorsque les données commencent par hasard à la ligne 1. Ne comptez pas sur cette coïncidence — faites soit le calcul Row + Count - 1, soit .End(xlUp) pour un vrai numéro de dernière ligne.

La règle de la contiguïté : CurrentRegion s'arrête aux vides

CurrentRegion saisit un unique îlot contigu. Une seule ligne ou colonne entièrement vide est un mur qu'il ne franchit pas. C'est un atout quand votre feuille contient un tableau entouré d'espace vide — vous obtenez exactement ce tableau, à partir de son vrai coin supérieur gauche, sans lignes fantômes :

Dim block As Range
Set block = ws.Range("A1").CurrentRegion   ' en-tête + toutes les données contiguës

Mais c'est un piège quand vos données comportent une ligne vide de séparation intentionnelle (un espace de sous-total, un séparateur visuel) : CurrentRegion ne renvoie que le morceau touchant votre cellule d'amorce, ratant silencieusement le reste. Et la cellule d'amorce doit être à l'intérieur du bloc — Range("A1").CurrentRegion, quand A1 est vide, ne renvoie que A1.

Le bon jugement : laquelle, et quand

Un seul tableau propre, avec en-tête et sans ligne vide interne → Range("A1").CurrentRegion. En direct, commence au vrai coin supérieur gauche, honnête sur les trous, sans lignes fantômes. C'est le choix par défaut pour « saisir mon tableau ».
« Effacer / réinitialiser / mettre en forme toute la feuille » → ws.UsedRange. La surestimation est sans danger — voire utile — quand vous effacez ou nettoyez la mise en forme.
« Combien de lignes de données ai-je ? » → ni l'une ni l'autre, directement. Utilisez la détection de dernière ligne (.End(xlUp) ou .Find) du guide sur la dernière ligne, ou faites le calcul Row + Count - 1 sur UsedRange.
Données avec des lignes vides de séparation intentionnelles → pas CurrentRegion (il s'arrête au premier trou). Trouvez la vraie dernière ligne et construisez la plage explicitement.

En résumé : CurrentRegion pour lire un tableau, UsedRange pour nettoyer une feuille, et la détection de dernière ligne pour compter les lignes.

Comment ExcelMaster aide

Saisir le bloc est toujours un moyen au service d'une fin — le trier, le dédoublonner, le copier, le résumer. ExcelMaster prend cet objectif final en langage courant — « nettoie tout le tableau des commandes et supprime les lignes en double » — et sélectionne les données de façon robuste, pour que vous ne livriez jamais une macro qui boucle sur 900 lignes vides mais formatées ni ne confonde un nombre de lignes avec un numéro de ligne.

Vous écrirez encore CurrentRegion au sein d'une macro planifiée. Mais pour les tâches ponctuelles « opère sur tout mon tableau », décrire l'opération évite toute la décision UsedRange contre CurrentRegion — et ses deux bugs célèbres.

Questions fréquentes

Quelle est la différence entre UsedRange et CurrentRegion en VBA ?

UsedRange est une boîte englobante mise en cache au niveau de la feuille, regroupant toutes les cellules qu'Excel a un jour touchées, cellules au formatage seul comprises ; elle surestime et ne rétrécit pas avant l'enregistrement. CurrentRegion est calculée en direct autour d'une cellule précise et s'étend jusqu'à rencontrer une ligne ou une colonne vide, renvoyant donc un unique bloc contigu. Utilisez CurrentRegion pour lire un tableau, UsedRange pour nettoyer une feuille.

Pourquoi mon UsedRange est-il plus grand que mes données ?

Parce que UsedRange compte les cellules qui n'ont qu'un formatage (bordures, remplissages) et ne rétrécit pas quand vous supprimez des valeurs — Excel conserve l'ancienne boîte englobante jusqu'à l'enregistrement du fichier. Supprimez entièrement les lignes ou colonnes superflues, ou utilisez Range("A1").CurrentRegion pour n'obtenir que les données contiguës.

UsedRange commence-t-il toujours à la cellule A1 ?

Non. UsedRange commence au coin supérieur gauche de la zone utilisée : si vos données débutent en C5, il commence en C5. C'est pourquoi UsedRange.Rows.Count est un nombre de lignes, pas le numéro de la dernière ligne — calculez la dernière ligne comme UsedRange.Row + UsedRange.Rows.Count - 1.

Pourquoi CurrentRegion rate-t-il une partie de mes données ?

CurrentRegion ne saisit que le bloc contigu autour de la cellule d'amorce, et il s'arrête à toute ligne ou colonne entièrement vide. Si vos données comportent une ligne vide de séparation intentionnelle, CurrentRegion ne renvoie que le morceau touchant votre cellule. Utilisez la détection de dernière ligne et construisez la plage explicitement pour des données avec des trous internes.

Comment sélectionner tout le tableau de données en VBA ?

Pour un seul tableau propre, Range("A1").CurrentRegion.Select saisit l'en-tête plus toutes les données contiguës, à partir du vrai coin supérieur gauche. Assurez-vous que la cellule d'amorce est à l'intérieur du tableau — une amorce vide ne renvoie que cette seule cellule.

Testé dans

Testé dans : Excel 365 (Windows 11), VBA 7.1 — dernière vérification le 30/07/2026.

Guides associés : VBA Last Row · VBA Offset · VBA Range · VBA For Loop