La suppression manuelle des doublons dans Excel (onglet Données, bouton Supprimer les doublons) ne répond pas au besoin réel : exécuter cette purge automatiquement à chaque ouverture du fichier. La seule approche native qui le permet repose sur l’événement VBA Workbook_Open(), déclenché par Excel dès le chargement du classeur. Nous détaillons ici la mise en œuvre concrète, les arbitrages techniques face à Power Query, et les garde-fous à poser pour éviter toute perte de données silencieuse.
Macro VBA Workbook_Open : supprimer les doublons Excel à l’ouverture
L’événement Workbook_Open() se place dans le module ThisWorkbook de l’éditeur VBA (Alt + F11). Toute procédure insérée dans cet événement s’exécute dès que le fichier est ouvert, avant même que l’utilisateur interagisse avec la feuille.
A découvrir également : Exporter données Access vers Excel : Tutoriel pas à pas facile et complet
Le code minimal pour enlever des doublons sur Excel à l’ouverture cible la méthode RemoveDuplicates d’un objet Range. Voici la structure :
Private Sub Workbook_Open()Dim ws As WorksheetSet ws = ThisWorkbook.Sheets("Feuil1")Dim lastRow As LonglastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Rowws.Range("A1:D" & lastRow).RemoveDuplicates Columns:=Array(1, 2, 3, 4), Header:=xlYesEnd Sub
Lire également : Excel moyenne pondérée pour les nuls : exemple concret à télécharger
Le paramètre Columns:=Array(1, 2, 3, 4) définit les colonnes prises en compte pour identifier un doublon. Adapter ce tableau est la première source d’erreur : oublier une colonne et Excel supprime des lignes qui ne sont en réalité pas des doublons.
Adapter la plage dynamiquement
Coder en dur la dernière ligne ("A1:D500") casse la macro dès que le fichier grossit. Calculer lastRow via End(xlUp) garantit que la plage s’ajuste à chaque ouverture, quel que soit le volume de données.
Pour un tableau structuré (ListObject), la syntaxe change : ws.ListObjects("MonTableau").DataBodyRange.RemoveDuplicates Columns:=Array(1, 2), Header:=xlNo. Le paramètre Header passe à xlNo parce que DataBodyRange exclut déjà la ligne d’en-tête.
Power Query ou VBA : quel outil pour enlever les doublons automatiquement
Power Query supprime les doublons de façon reproductible et traçable. Chaque étape est visible dans l’éditeur de requêtes, ce qui facilite l’audit. En revanche, Power Query ne se déclenche pas à l’ouverture du fichier sans intervention supplémentaire. L’actualisation automatique au chargement se configure dans les propriétés de la connexion (Données > Propriétés de la connexion > cocher « Actualiser les données lors de l’ouverture du fichier »), mais cette option rafraîchit la requête, elle ne modifie pas les données source en place.
La distinction est fondamentale. VBA avec Workbook_Open() modifie directement le tableau source en supprimant physiquement les lignes. Power Query, lui, produit un tableau de résultat dédoublonné dans une feuille séparée, sans toucher à la source. Le choix dépend du flux de travail :
- Si le fichier est alimenté manuellement ou par import CSV et que les données source doivent rester propres en permanence, VBA est la réponse directe.
- Si les données proviennent de sources externes (bases de données, fichiers partagés) et que la traçabilité des transformations prime, Power Query offre un historique d’étapes qu’une macro ne fournit pas nativement.
- Si le classeur est partagé en co-édition sur SharePoint ou OneDrive, les macros VBA sont désactivées par défaut. Power Query reste alors la seule option viable, combinée à l’actualisation automatique à l’ouverture.
Sécuriser la suppression automatique des doublons dans Excel
Supprimer des lignes sans confirmation utilisateur à chaque ouverture du fichier est un comportement risqué. Une ligne identifiée comme doublon par la macro peut être une donnée légitime si les colonnes de comparaison sont mal définies.
Nous recommandons d’intégrer systématiquement une copie de sauvegarde avant la purge, directement dans la macro :
ThisWorkbook.SaveCopyAs ThisWorkbook.Path & "\backup_" & Format(Now, "yyyymmdd_hhmmss") & ".xlsm"
Cette ligne, placée avant l’appel à RemoveDuplicates, crée un fichier horodaté dans le même dossier. En cas de suppression abusive, la restauration est immédiate.
Journal de suppression pour tracer les lignes supprimées
Pour aller plus loin, compter les lignes avant et après la purge permet de détecter les anomalies. Si le delta dépasse un seuil défini (par exemple plus d’un certain pourcentage du total), la macro peut afficher un MsgBox d’alerte au lieu de sauvegarder silencieusement.
Le principe : stocker lastRow avant la suppression, recalculer après, puis comparer. Un écart anormalement élevé signale souvent un problème dans le paramétrage des colonnes de comparaison, pas un vrai pic de doublons.
Formule UNIQUE et tableau dynamique : alternative sans macro
Excel 365 propose la fonction UNIQUE(), qui extrait les valeurs distinctes d’une plage dans un tableau dynamique (dynamic array). Cette formule se recalcule automatiquement à l’ouverture du fichier sans VBA ni Power Query.
La limite : UNIQUE() ne supprime pas les doublons de la source, elle les filtre dans une plage séparée. Les données d’origine restent intactes, ce qui peut être un avantage (conservation) ou un inconvénient (le fichier conserve du poids inutile). Pour un fichier volumineux alimenté en continu, cette approche alourdit le classeur puisque les lignes en double restent physiquement présentes.
La fonction accepte un paramètre de colonnes multiples : =UNIQUE(A2:D500) compare l’ensemble des colonnes A à D. Combinée avec SORT() ou FILTER(), elle produit un tableau de sortie propre, recalculé à chaque modification ou ouverture.
Enregistrer le fichier au format xlsm pour conserver la macro
Un classeur contenant du code VBA doit être enregistré au format .xlsm (classeur Excel prenant en charge les macros). Un enregistrement en .xlsx supprime silencieusement tout le code VBA, y compris l’événement Workbook_Open().
Autre point de friction : les paramètres de sécurité des macros. Si le Centre de gestion de la confidentialité bloque les macros, l’automatisation ne se déclenche pas. Placer le fichier dans un emplacement approuvé (Fichier > Options > Centre de gestion de la confidentialité > Emplacements approuvés) permet de contourner cette restriction sans abaisser le niveau de sécurité global.
L’automatisation de la suppression des doublons à l’ouverture d’un fichier Excel repose sur un arbitrage clair entre modification directe des données (VBA), transformation traçable (Power Query) et filtrage non destructif (UNIQUE). Le choix du format de fichier et la sauvegarde préventive conditionnent la fiabilité de l’ensemble du dispositif.
