🚀The world's best VBA AI has evolved. ExcelMaster is now an autonomous Agent.Read more →
Back to Blog

VBA SpecialCells dans Excel — sélectionner cellules vides, cellules visibles et constantes (et pourquoi il plante quand il ne trouve rien)

|

VBA SpecialCells dans Excel — sélectionner cellules vides, cellules visibles et constantes (et pourquoi il plante quand il ne trouve rien)

TL;DR — SpecialCells laisse Excel choisir les cellules par type plutôt que par adresse : toutes les vides, toutes les cellules visibles, toutes les formules, toutes les constantes. C'est le jumeau en code d'Accueil → Rechercher et sélectionner → Atteindre les cellules. La règle qui compte le plus : quand il ne trouve rien, il ne renvoie pas une plage vide — il lève error 1004 « No cells were found ». Un appel nu est donc une bombe à retardement. Enveloppez-le toujours :

Dim blanks As Range
On Error Resume Next
Set blanks = ThisWorkbook.Worksheets("Data").Range("A2:A1000").SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not blanks Is Nothing Then blanks.Value = 0   ' ne s'execute que si des cellules vides ont ete trouvees

Chaque référence que vous avez construite jusqu'ici décrit un seul rectangle — Range("A1:D100"), Cells(r, c), un bloc découpé avec Resize ou CurrentRegion. Mais le travail réel veut rarement un rectangle. Il veut toutes les cellules vides à remplir, seulement les lignes visibles après un filtre, juste les formules à verrouiller. SpecialCells, c'est ainsi que vous les demandez : vous cessez de décrire une adresse pour commencer à décrire quel type de cellule vous voulez. Ce guide s'articule autour d'une seule idée — SpecialCells est un filtre qui vous rend une référence, et « rien ne correspond » est une erreur, pas un ensemble vide. Acquérez-la et tous les autres pièges disparaissent.

Ce que vous allez apprendre

  • Le modèle mental — SpecialCells choisit les cellules par type, la version en code d'Atteindre les cellules
  • La règle la plus importante — il lève error 1004 quand rien ne correspond, alors protégez chaque appel
  • Les types de cellules dont vous vous servez vraiment — vides, visibles, constantes, formules, dernière cellule
  • Le motif remplir-les-vides — xlCellTypeBlanks plus une formule d'une ligne
  • Le motif copier-les-lignes-visibles — xlCellTypeVisible après un AutoFilter (le sauveur du nombre de lignes)
  • Pourquoi le résultat est souvent non contigu (plusieurs Areas) et ce que cela change

Le modèle mental : SpecialCells choisit les cellules par type, pas par adresse

Range et Cells répondent à quelle cellule par un emplacement — une chaîne ou deux nombres. SpecialCells répond à une autre question : quelles cellules d'un certain type ? Vous lui remettez une zone à parcourir et une constante de type, Excel balaie cette zone et vous rend une référence vers chaque cellule qui qualifie :

Dim used As Range: Set used = ActiveSheet.UsedRange
used.SpecialCells(xlCellTypeFormulas).Interior.Color = vbYellow   ' surligne chaque formule

Si vous avez déjà utilisé Atteindre les cellules (appuyez sur F5, puis Cellules...), c'est exactement cette boîte de dialogue en code — « Constantes », « Formules », « Cellules vides », « Cellules visibles seulement » sont les mêmes options. La force, c'est qu'Excel fait le balayage : vous ne parcourez jamais la feuille en demandant « celle-ci est-elle vide ? » — vous demandez toutes les vides d'un coup, et Excel vous les renvoie sous forme d'une plage unique (souvent de forme biscornue).

La règle la plus importante : il plante quand il ne trouve rien

Voici la ligne qui transforme une macro qui marche en plantage. Quand aucune cellule de la zone parcourue ne correspond au type, SpecialCells ne renvoie pas une plage vide — il lève error 1004, « No cells were found ». Une plage sans aucune cellule vide, passée dans SpecialCells(xlCellTypeBlanks), arrête net votre macro :

' FRAGILE - plante avec error 1004 le jour ou il n'y a aucune cellule vide :
Range("A2:A1000").SpecialCells(xlCellTypeBlanks).Value = 0

C'est voulu, pas un bug : SpecialCells traite « aucune correspondance » comme une condition exceptionnelle plutôt que comme un ensemble vide. Cela signifie que la forme sûre n'est pas optionnelle — c'est la seule forme correcte. La protection en trois temps : activez la gestion des erreurs, exécutez l'appel, désactivez-la de nouveau, puis testez Nothing :

Dim hits As Range
On Error Resume Next
Set hits = Range("A2:A1000").SpecialCells(xlCellTypeBlanks)
On Error GoTo 0                       ' cesser aussitot d'avaler les erreurs
If hits Is Nothing Then
    MsgBox "No blank cells to fill."
Else
    hits.Value = 0
End If

On Error Resume Next ne couvre que la seule ligne à risque ; On Error GoTo 0 rétablit aussitôt le signalement normal des erreurs, si bien que vous n'ignorez pas en silence d'autres bugs (voir VBA On Error). Sautez la protection et votre macro est une bombe à retardement : elle marche sur chaque fichier de test comportant des vides et explose sur le premier fichier sans vide.

Les types de cellules qui comptent

SpecialCells(Type, [Value]) prend une constante de type, et une poignée couvre presque tout :

rng.SpecialCells(xlCellTypeBlanks)         ' cellules vides a l'interieur de la zone
rng.SpecialCells(xlCellTypeVisible)        ' cellules non masquees par un filtre ou des lignes masquees
rng.SpecialCells(xlCellTypeConstants)      ' valeurs saisies (nombres, texte) - pas des formules
rng.SpecialCells(xlCellTypeFormulas)       ' cellules qui contiennent une formule
rng.Cells.SpecialCells(xlCellTypeLastCell) ' le coin inferieur droit de la plage utilisee

xlCellTypeConstants et xlCellTypeFormulas acceptent un second argument optionnel pour affiner par type de résultat — SpecialCells(xlCellTypeFormulas, xlErrors) ne saisit que les formules qui renvoient actuellement une erreur, ce qui est la manière la plus rapide de trouver chaque #REF! ou #DIV/0! d'une feuille :

Dim bad As Range
On Error Resume Next
Set bad = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0
If Not bad Is Nothing Then bad.Interior.Color = vbRed   ' signale d'un coup chaque formule en erreur

Le motif remplir-les-vides

La raison classique pour laquelle on se tourne vers SpecialCells, c'est de combler les trous d'un rapport — répéter une étiquette sous chaque cellule vide, ou mettre à zéro les nombres vides. Sélectionnez les vides, puis écrivez dans toute la sélection en une seule fois. Pour recopier la valeur de la cellule du dessus de chaque vide, employez une formule R1C1 relative puis convertissez en valeurs :

Dim gaps As Range
On Error Resume Next
Set gaps = Range("A2:A5000").SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not gaps Is Nothing Then
    gaps.FormulaR1C1 = "=R[-1]C"      ' chaque vide = la cellule juste au-dessus
    gaps.Value = gaps.Value          ' fige les formules en valeurs statiques
End If

Cela comble des milliers de trous en deux lignes sans aucune boucle. FormulaR1C1 = "=R[-1]C" signifie « une ligne au-dessus, même colonne », écrit dans chaque vide d'un coup ; la seconde ligne remplace les formules par leurs résultats pour que le remplissage survive à un tri.

Le motif copier-les-lignes-visibles (le sauveur du nombre de lignes)

xlCellTypeVisible est le type le plus utile à lui seul, à cause de ce que fait VBA sans lui. Après avoir appliqué un AutoFilter, les lignes écartées par le filtre sont masquées, pas supprimées — et un simple .Copy les copie quand même. Pour n'agir que sur ce que l'utilisateur voit, vous devez passer par SpecialCells(xlCellTypeVisible) :

' Copier uniquement les lignes que le filtre laisse visibles - les lignes masquees sont ignorees :
ws.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy _
    Destination:=Sheets("Summary").Range("A1")

Sans xlCellTypeVisible, cela copie tout le bloc, lignes masquées comprises, et vous collez en silence des données que l'utilisateur avait filtrées. C'est le pont entre le groupe d'adressage et le vrai travail de filtrage : CurrentRegion trouve le bloc, SpecialCells(xlCellTypeVisible) le restreint aux lignes visibles. La même idée supprime les lignes filtrées : filtrez, puis .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete.

Le piège des multi-zones : le résultat n'est souvent pas un rectangle

Voici ce qui distingue SpecialCells de toutes les références qui précèdent : la plage qu'il renvoie n'est généralement pas contiguë. Dix cellules vides éparpillées reviennent sous forme d'une référence faite de dix zones distinctes. Cela change la façon de l'inspecter :

Dim vis As Range: Set vis = rng.SpecialCells(xlCellTypeVisible)
Debug.Print vis.Count            ' total des cellules visibles sur TOUTES les zones
Debug.Print vis.Areas.Count      ' combien de blocs distincts cela represente
Dim a As Range
For Each a In vis.Areas
    Debug.Print a.Address        ' chaque bloc contigu, un a la fois
Next a

.Count est le total général des cellules ; .Areas.Count est le nombre de blocs disjoints qu'elles forment. La plupart des opérations — définir une valeur, une couleur, un .Copy vers une autre feuille — traitent toutes les zones d'un coup, si bien que vous bouclez rarement. L'exception, c'est tout ce qui est sensible à l'ordre : si vous supprimez des lignes trouvées par SpecialCells, supprimez .EntireRow en un seul appel plutôt qu'en bouclant vers le haut, car l'ordre des zones n'est pas garanti de bas en haut. Cette forme multi-zones est partagée avec Union, qui construit exprès le même genre de référence non contiguë.

Comment ExcelMaster aide

SpecialCells est puissant précisément parce qu'il est tranchant : oubliez la protection On Error et un fichier propre fait planter votre macro ; oubliez xlCellTypeVisible et vous copiez les lignes qu'un filtre masquait ; traitez le résultat comme un rectangle et une boucle cellule par cellule atterrit dans le mauvais ordre. Chacun de ces cas est un échec silencieux et circonstanciel — ça marche sur vos données et casse sur celles d'un autre.

ExcelMaster vous laisse plutôt décrire le résultat. Dites « remplis chaque cellule vide de la colonne A avec la valeur du dessus » ou « copie seulement les lignes visibles vers une feuille Summary », et il écrit l'appel SpecialCells protégé, la vérification Is Nothing et le routage vers les seules cellules visibles — la version qui survit à un fichier sans aucune cellule vide et à un filtre qui masque la moitié des lignes. Vous gardez le classeur et le code ; vous vous épargnez le plantage au premier cas limite.

Questions fréquentes

Pourquoi SpecialCells donne-t-il une erreur « No cells were found » ?

Parce que SpecialCells lève error 1004 quand aucune cellule de la zone parcourue ne correspond au type demandé — il traite « aucune correspondance » comme une erreur, pas comme une plage vide. Une zone sans aucune cellule vide passée dans SpecialCells(xlCellTypeBlanks) arrêtera la macro. Enveloppez l'appel dans On Error Resume Next, rétablissez avec On Error GoTo 0, et testez If Not result Is Nothing avant de vous en servir.

Comment sélectionner uniquement les cellules visibles après un filtre en VBA ?

Utilisez xlCellTypeVisible : rng.SpecialCells(xlCellTypeVisible). Après un AutoFilter, les lignes écartées par le filtre sont masquées mais font toujours partie de la plage, si bien qu'un simple .Copy les inclut. Passer par SpecialCells(xlCellTypeVisible) restreint la copie, la couleur ou la suppression aux lignes que l'utilisateur voit réellement.

Quelle est la différence entre xlCellTypeConstants et xlCellTypeFormulas ?

xlCellTypeConstants renvoie les cellules qui contiennent une valeur saisie — des nombres ou du texte tapés directement. xlCellTypeFormulas renvoie les cellules qui contiennent une formule. Toutes deux acceptent un second argument optionnel pour affiner par type de résultat, donc SpecialCells(xlCellTypeFormulas, xlErrors) ne renvoie que les cellules de formule affichant actuellement une erreur comme #REF! ou #DIV/0!.

Comment remplir toutes les cellules vides d'un coup en VBA ?

Sélectionnez les vides avec SpecialCells(xlCellTypeBlanks) (protégé contre l'erreur d'absence de vides), puis écrivez dans toute la sélection en une seule instruction. Pour recopier la valeur au-dessus de chaque vide, employez gaps.FormulaR1C1 = "=R[-1]C" puis gaps.Value = gaps.Value pour figer les formules en valeurs statiques. Aucune boucle n'est nécessaire.

SpecialCells renvoie-t-il une plage contiguë ?

Généralement non. Les cellules vides ou visibles que vous demandez sont souvent éparpillées, si bien que SpecialCells renvoie une référence multi-zones. .Count est le nombre total de cellules sur toutes les zones, et .Areas.Count est le nombre de blocs distincts qu'elles forment. La plupart des opérations traitent toutes les zones d'un coup ; ne bouclez For Each a In result.Areas que lorsque vous avez besoin de chaque bloc contigu séparément.

Testé dans

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

Guides associés : VBA Union · VBA Intersect · VBA AutoFilter · VBA CurrentRegion · VBA On Error