Vous ouvrez un fichier Excel et, à chaque fois, les mêmes lignes vides traînent entre vos données. Les supprimer à la main prend du temps, surtout quand le fichier dépasse plusieurs milliers de lignes. La bonne approche consiste à automatiser cette suppression pour qu’elle se déclenche dès l’ouverture du classeur. Deux outils permettent d’y arriver : une macro VBA liée à l’événement d’ouverture, ou Power Query intégré au fichier.
Pourquoi les lignes vides persistent dans un fichier Excel
Avant d’automatiser quoi que ce soit, il faut comprendre ce qui rend ces lignes si tenaces. Une cellule qui paraît vide ne l’est pas toujours aux yeux d’Excel.
Certaines cellules contiennent un espace invisible, un retour à la ligne ou une formule qui renvoie une chaîne vide (=""). Ces cellules passent à travers la plupart des méthodes de sélection rapide. Si vous utilisez « Rechercher et sélectionner > Cellules vides », elles ne seront pas détectées.
Tester si une ligne est réellement vide
La fonction NBCAR() appliquée à une cellule suspecte renvoie 0 si la cellule est vide, et un chiffre supérieur si elle contient un espace ou un caractère masqué. C’est le test le plus fiable avant de lancer un nettoyage automatisé.
Autre piège fréquent : les lignes insérées par un copier-coller depuis une source externe (un export CSV, une base de données). Ces lignes héritent parfois d’une mise en forme sans donnée visible, ce qui les rend difficiles à repérer dans un grand tableau.
Macro VBA Workbook_Open pour supprimer les lignes vides automatiquement
L’événement Workbook_Open exécute du code VBA à chaque ouverture du classeur. C’est l’endroit idéal pour placer une routine de nettoyage.
Le code à placer dans ThisWorkbook
Ouvrez l’éditeur VBA (Alt + F11), double-cliquez sur ThisWorkbook dans l’explorateur de projet, puis collez ce code :
Private Sub Workbook_Open()Dim ws As WorksheetSet ws = ThisWorkbook.Sheets(1)Dim lastRow As LonglastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).RowDim i As LongFor i = lastRow To 1 Step -1If Application.WorksheetFunction.CountA(ws.Rows(i)) = 0 Thenws.Rows(i).DeleteEnd IfNext iEnd Sub
La boucle parcourt le tableau de bas en haut. Ce sens est capital : supprimer de bas en haut évite de décaler les index et de sauter des lignes pendant le traitement.
La fonction CountA compte toutes les cellules non vides d’une ligne. Si le résultat est 0, la ligne entière est supprimée. Cette vérification garantit qu’on ne touche qu’aux lignes totalement vides, pas à celles qui contiennent une seule donnée dans une colonne éloignée.
Points de vigilance
- Le fichier doit être enregistré au format .xlsm (classeur prenant en charge les macros). Un fichier .xlsx bloquera l’exécution du code sans avertissement clair.
- Si vos données commencent à la colonne B ou C, adaptez la référence
Cells(ws.Rows.Count, 1)pour pointer vers la bonne colonne de départ. - Sur un fichier volumineux, la macro peut prendre quelques secondes. Ajoutez
Application.ScreenUpdating = Falseen début de procédure etTrueà la fin pour accélérer le traitement.
PERSONAL.XLSB : appliquer le nettoyage à tous vos fichiers Excel
Placer la macro dans chaque classeur fonctionne, mais c’est contraignant si vous travaillez avec des dizaines de fichiers différents. Le classeur PERSONAL.XLSB résout ce problème.
Ce classeur masqué se charge automatiquement à chaque démarrage d’Excel. Toute macro stockée dedans est disponible dans n’importe quel fichier ouvert, sans duplication de code.
Mise en place concrète
Si PERSONAL.XLSB n’existe pas encore sur votre poste, enregistrez une macro quelconque via l’onglet Développeur en choisissant « Classeur de macros personnelles » comme destination. Excel crée alors le fichier automatiquement.
Ensuite, ouvrez l’éditeur VBA, repérez le projet PERSONAL.XLSB, et placez-y un module contenant votre routine de suppression. Pour qu’elle se déclenche à l’ouverture de chaque classeur, utilisez l’événement Workbook_Open dans le module ThisWorkbook de PERSONAL.XLSB, ou créez un gestionnaire d’événements applicatif (classe Application avec WithEvents).
La limite : cette approche nécessite que les macros soient activées sur le poste. Dans un environnement professionnel avec des restrictions de sécurité, vérifiez les paramètres du Centre de gestion de la confidentialité.
Power Query : supprimer les lignes vides sans macro VBA
Vous n’avez pas accès aux macros, ou vous préférez éviter le code ? Power Query offre une alternative native, disponible depuis Excel 2016.
Power Query ne modifie pas directement la feuille source. Il crée une requête de transformation qui se rafraîchit à chaque ouverture du fichier (si vous activez l’option).
Créer la requête de nettoyage
Sélectionnez votre tableau, puis allez dans Données > À partir d’un tableau/plage. L’éditeur Power Query s’ouvre. Cliquez sur « Supprimer les lignes > Supprimer les lignes vides ». Fermez et chargez le résultat dans une nouvelle feuille ou à la place des données d’origine.
Pour que le nettoyage soit automatique à l’ouverture : dans les propriétés de la connexion (Données > Propriétés), cochez « Actualiser les données lors de l’ouverture du fichier ». À chaque ouverture, Power Query relance la transformation et élimine les lignes vides sans intervention.
Quand préférer Power Query à une macro
- Votre entreprise bloque l’exécution des macros VBA pour des raisons de sécurité.
- Le fichier est partagé sur OneDrive ou SharePoint, où les macros ne s’exécutent pas toujours.
- Vous souhaitez combiner la suppression des lignes vides avec d’autres transformations (suppression de doublons, changement de type de données).
Power Query gère aussi les cellules contenant des espaces invisibles, à condition d’ajouter une étape « Supprimer les espaces » (fonction Text.Trim) avant la suppression des lignes vides.
Macro ou Power Query : quel choix selon votre contexte
| Critère | Macro VBA | Power Query |
|---|---|---|
| Activation automatique à l’ouverture | Oui (Workbook_Open) | Oui (actualisation auto) |
| Format de fichier requis | .xlsm obligatoire | .xlsx suffit |
| Partage sur OneDrive/SharePoint | Limité | Compatible |
| Détection des faux vides (espaces) | Nécessite un ajout de code | Natif avec Text.Trim |
| Application à plusieurs fichiers | Via PERSONAL.XLSB | Requête à recréer par fichier |
Pour un fichier local que vous êtes seul à utiliser, la macro VBA reste la solution la plus directe. Pour un fichier partagé ou en environnement restreint, Power Query est plus fiable.
Les deux approches se complètent. Rien n’empêche de configurer Power Query pour le nettoyage structurel et d’ajouter une courte macro pour des traitements complémentaires. L’objectif reste le même : ouvrir le fichier et trouver un tableau propre, sans manipulation supplémentaire.
