Monsieur Excel
Pour tout savoir faire sur Excel !

12 janvier 2013

Macro pour nettoyer une table

L’expérience que nous allons faire aujourd’hui est à la fois simple et inquiétante…

Nous avons en A1:D1001 une simple table dans laquelle, comme nous pouvons le constater dans la barre de formule sur la copie d’écran, la colonne D  effectue la somme des contenus des colonnes B et C.

Nous souhaitons détruire le contenu de la base et, afin de rendre cette opération facilement renouvelable dans le futur, nous enregistrons une macro – sous le nom « Efface_base » - pour effectuer ce travail.

Pour réaliser cela, nous effectuons les opérations suivantes :
-          Sélection du bloc A2:D1001
-          Enregistrement de la macro
-          Clic droit sur la sélection
-          Commande « Supprimer » puis « Lignes de tableau »
-          Arrêt de l’enregistrement

Remarque – Nous aurions certes pu inclure la sélection du bloc dans la macro, mais il aurait alors fallu la rendre élastique… Ce n’est pas ce qui nous intéresse dans cet exemple, ce qui explique pourquoi je ne l’ai pas fait ici.

Nous consultons la macro enregistrée et nous constatons qu’elle est constituée de 1.000 lignes identiques avec chacune la commande « Selection.ListObject.ListRows(1).Delete ».

Cela est certes tout sauf efficace !

Pire encore, si nous rétablissons la table, re-sélectionnons le bloc A2:D1001 puis lançons la macro, nous constatons que son exécution prend au total 15 secondes !

Bon, nous savions déjà que les macros enregistrées sont réputées pour être inefficaces, mais – là ! – il s’agit d’un record toutes catégories !

A titre indicatif, la macro suivante, qui revient exactement au même s’il n’y a rien à droite de la table, prend une seule ligne et s’exécute instantanément…
Rows("2:1001").Delete Shift:=xlUp


rédigé par Hervé Thiriez | 7 commentaire(s)

7 Commentaire(s):

  • Comment faites-vous pour obtenir une telle macro (c'est une vraie question).

    Moi, lorsque je crée la macro qui me supprime les lignes 1 à 1001, que ça soit sous Office 2003 ou Office 2010, voici ce que j'obtiens :

    Rows("1:1001").Select
    Selection.Delete Shift:=xlUp

    Bon, toujours l'étape Select/Selection inutile, mais au moins, la macro semble honnête.



    Votre macro fait apparaître un ListObject.listrow ... Il s'agit donc d'un objet d'Excel et non pas une simple table dans des colonnes Excel.
    http://msdn.microsoft.com/fr-fr/library/microsoft.office.tools.excel.listobject.listrows%28v=vs.80%29.aspx

    Vos 2 copies d'écran (Table Excel et macro) ne semblent pas du tout correspondre.


    En résumé, je suis dubitatif et je ne comprends pas ce que vous nous montrez.

    Molenn

    By Anonymous Anonyme, sur 9:05 AM  

  • Il doit s'agir d'une table structurée ce qui expliquerait la macro et la lenteur (si on peut toutefois justifier une telle lenteur !!)

    Jeremy

    By Blogger Jérémy Laplaine, sur 10:29 AM  

  • Je n'y avais même pas pensé (honte à moi)

    Nouvel essai : tableau structuré (en utilisant le menu Insertion\tableau du ruban de mon Office 2010.

    Je sélectionne les lignes 1:1001 et je supprime, la macro obtenue :
    Rows("1:1001").Select
    Selection.Delete Shift:=xlUp

    Je sélectionne cette fois les cellules A1:D1001 et je supprime avec le menu contextuel les lignes du tableau.
    Macro obtenue :
    Range("Tableau1").Select
    Selection.ListObject.ListRows(1).Delete
    Selection.ListObject.ListRows(1).Delete
    Selection.ListObject.ListRows(1).Delete
    Selection.ListObject.ListRows(1).Delete
    Selection.ListObject.ListRows(1).Delete
    Selection.ListObject.ListRows(1).Delete
    Selection.ListObject.ListRows(1).Delete
    Selection.ListObject.ListRows(1).Delete
    Selection.ListObject.ListRows(1).Delete
    Selection.ListObject.ListRows(1).Delete
    Selection.ListObject.ListRows(1).Delete
    Selection.ListObject.ListRows(1).Delete

    On retrouve bien le cas, mais il faut alors aussi préciser que l'on ne fait pas la même chose.
    En tout cas, c'est plus clair pour moi à présent.

    Molenn

    By Anonymous Anonyme, sur 11:04 AM  


  • Si vous aviez fait exactement ce que j'ai indiqué, à savoir :
    - Sélection du bloc A2:D1001
    - Clic droit sur la sélection
    - Commande « Supprimer » puis « Lignes de tableau »

    ... ce ne pouvait être que sur un tableau structuré et vous auriez eu le même résultat que moi.

    By Blogger Hervé Thiriez, sur 12:26 AM  

  • Plus général (pas de bloc à sélectionner, peu importe qu'il y ait ou non des colonnes à droite de la colonne à supprimer) :
    ActiveSheet.ListObjects("Tableau1").ListColumns(4).Delete

    ou

    ActiveSheet.ListObjects(1).ListColumns(4).Delete

    By Anonymous cduigou, sur 4:47 PM  

  • Bonjour,

    Pour supprimer le contenu d'un listobject il faut le sélectionner avec la propriété: DataBodyRange.

    cf. article: http://colinlegg.wordpress.com/2012/09/01/clearing-excel-tables/

    Edouard

    By Blogger Unknown, sur 8:58 AM  

  • Bonjour,

    La solution d'Edouard est 100 fois meilleurs!!

    With Sheets("Feuil1").ListObjects("Tableau1")
    If Not .DataBodyRange Is Nothing Then
    .DataBodyRange.ClearContents
    .DataBodyRange.Delete
    End If
    End With

    Ça vaudrait la peine de corriger car si on a des choses à côté du tableau on les supprimes aussi avec la solution du Row().Selection!

    Christian

    By Anonymous Anonyme, sur 3:33 PM  

Enregistrer un commentaire

<< Accueil