Macro pour alléger fichier

Question

J’espère que vous allez bien. Je voulais savoir s’il existait une macro pour alléger un fichier Excel de plusieurs onglets ; je m’explique : en partant du constat que les cellules vides sur une plages alourdissent un fichier que les mises en forme également Etc etc Je m’interrogeais pour savoir s’il etait possible de admettons que j’ai une feuille Excel avec une plage de A1:AZ999 (on le voit grâce à l’ascenseur Excel) sur laquelle j’ai des données en cellule B12:AA210 avec des mises en forme simple (pas de conditionnelles, gras etc etc) L’idée serait au choix : 1) supprimer les cases vides de la plage AA211:AZ:9999 et B211:AZ999 2) convertir si souhaite les donnés B12:AA210 en valeurs sans mises en formes 3) 1&2 ensemble 4) 3 et réaliser une sauvegarde de l’onglet original et d’accepter la réalisation 5) généraliser le traitement sur une sélection de plusieurs onglets Ce sont des idées il y a sûrement des paramètres auxquels je n’ai pas pensé, et suis preneurs pour toutes suggestions, idées, amélioration ! Excellente journée, Naxos...

Ausecour · Answer

Je ne connais pas de macro déjà existante pour faire ce que tu souhaites, mais ça ne me semble vraiment pas compliqué à faire... Au lancement de ta macro, tu choisis si tu souhaites supprimer la mise en forme, garder la feuille d'origine ou non, tu chosis ensuites tous les onglets que tu veux traiter, et tu valides. Pour réellement supprimer les cellules vides qui prennent de la place en mémoire, je copie le contenu d'une feuille, puis je supprime l'ancienne, et je renomme la feuille créee en lui donnant le nom de l'ancienne, ça peut quasiment totalement se programmer avec l'enregistreur de macro en plus, seule la partie choix demande un peu de codage pur...

Ausecour · Answer

Re, Si tu n'as pas de trou dans tes données, on peut utiliser le code suivant pour connaître la dernière cellule remplie en bas à droite: set CelFin = Range("a1").End(xlToRight).End(xlDown) Ainsi tu pourras conserver uniquement les données qui se trouvent entre A1 et cette cellule Avoir un fichier avec ta macro actuelle et des données qui représentent bien ta situation m'aiderait bien à comprendre clairement toute ta problématique, il y a peut-être des contraintes que je ne vois pas depuis le forum.....

Ausecour · Answer

J'ai une question par rapport au fichier que tu m'as transmis... Je vois que tous tes exemples se ressemblent, est-ce que c'est aussi le cas pour ton fichier réel? parce que si c'est le cas j'ai juste à délimiter les deux tableaux que tu veux conserver et gérer la suite via macro. Tu veux conserver des commentaires sur des cellules vides? la cellule en AJ qui est vide contient un commentaire marqué "ne pas supprimer", je dois avouer ne pas comprendre pourquoi tu veux garder ce commentaire... Visiblement dans tes exemples c'est toujours la même zone qui est conservé, le tableau en dessous tu l'enlèves aussi, ça peut facilement être automatisé, pour la suppression des formats ou non, des données ou non etc... En clair tu voudrais pour chaque feuille à traiter sélectionner la zone à garder, indiquer ce que tu veux faire sur la feuille, puis passer à la suivante? J'ai besoin de savoir tout ça pour automatiser la macro au maximum, j'aurai peut-être d'autres questions à te poser par la suite...

Ausecour · Answer

Re,

Je vais essayer de traduire les éléments que tu m'as fournis:

c’est vrai que j’ai pas modifié l’exemple mais oui tu fais bien, l’idée c’est que mes plages de données vont toutes bouger ! Rien ne sera fixé dans mon fichier (je peux pas te le mettre à dispo malheureusement...) ; sur onglet 1 ça sera plages A1:Z99 et donc supprimer de A100:fin de page et Z100:fin de page ; sur l’onglet 2 ça sera B10:AZ100 et donc supprimer AZ101:fin et B101:fin ; etc etc

En clair, si tu as choisis de supprimer les cases vides hors zone de données, il faudra demander pour chaque feuille cochée, quelle est la plage de cellules à conserver.

L’idée serait de lancer la macro qui invite à effectuer les actions suivantes (case à cocher) :

Puis cliquer sur OK

Si choix 4 ou 5 demander une seconde confirmation

En clair la macro appelle un formulaire de saisie qui appellera ensuite une autre macro chargée d'alléger ton fichier, le formulaire aura des cases à cocher:

supprimer tous les formats de la feuille
supprimer les cases vides hors zone de données
sélectionner toutes les cases vides de données (pas compris cette demande...)

Les choix 3 et 5 ne servent pas.

Étape 2

Puis cliquer sur OK

Une liste déroulante qui permettra de choisir parmi toutes les feuilles du classeur, tu cliques simplement sur les feuilles à traiter, un bouton "tout sélectionner" et un bouton "tout désélectionner"

Étape 3

Et lancer le traitement sur classeur.xlsx

Je n'ai pas vu ce paramètre dans tes choix, mais ce serait à ajouter au formulaire, une case à cocher "archiver une copie du fichier". C'est assez simple à faire normalement, la seule partie "tricky" c'est de faire comprendre à Excel que le fichier que tu enregistres sous un autre nom ne doit pas remplacer le classeur actif, chose qu'Excel fait naturellement quand tu utilises enregistrer sous, de mémoire c'est dans les paramètres de l'enregistrement que tu peux t'assurer de ça, je ne suis plus bien sûr.

Étape 4 :si erreur sur une feuille lors du traitement (commentaire en dehors de la zone de donnés, objets etc etc) ne pas appliquer le traitement et passer à la feuille suivante

Cette étape risque de poser problème... Sur ma version Excel, j'ai 16 384 colonnes et 1 048 576 lignes soit 17 179 869 184 cellules... On demande à l'utilisateur de sélectionner les zones à garder, mais si il oublie des valeurs on ne traite pas la feuille? et pour ça il faut aller vérifier sur toute la feuille si il reste un commentaire ou une valeur qui trainerait? Ce serait à la fois long et sans intérêt par rapport aux informations qu'on demande à l'utilisateur. "Sélectionnez la plage de valeurs à garder, mais attention, on va vérifier chaque cellule de la feuille pour être sûr que vous n'avez pas oublié des cellules", ça revient un peu à dire ça...

Étape 5: générer un onglet avec le recap (ls_errors_manual) des feuilles sur lesquelles le traitement n’a pas été fait pour un ajustement manuel si nécessaire (une case isolée avec une donnée ou un commentaire ou un objet etc.)

Cette partie rejoint les commentaires que j'ai fait sur l'étape précédente.

Étape 6: pour toutes les feuilles traitées placer le curseur en A1, démasquer toutes les cellules masquées, sauvegarder le fichier classeur.xlsx

Facile à faire

Étape 7: afficher un message de succès si pas d’erreur ou afficher le message traitement partiel intervention manuelle nécessaire et se placer sur onglet ls_error_manual

Cette partie rejoint les commentaires faits sur les étapes 4 et 5, je ne la trouve pas vraiment utile.

Ausecour · Answer

Bonjour, On voit plus clair déjà, c'est bien Par rapport à ce que tu dis pour le 1), j'ai pensé à quelque chose... Peu importe la façon dont les données sont mises, on s'attend plus ou moins à ce qu'elles se trouvent dans une zone donnée non? que ce soit les commentaires ou les valeurs/formules. On pourrait donc concentrer la macro sur cette zone sans demander à chaque fois à l'utilisateur quelle plage garder, on garderait ainsi toutes les valeurs, commentaires, se trouvant dans cette zone, et les objets on peut tous les parcourir avec la collection Shapes. Mais est-ce qu'il est possible de définir une telle zone? A1:AJ100 par exemple? Pour copier même les valeurs qui sont ="", on peut passer par Range.Formula, ça renverra bien la formule ="". 2) En fait c'est surtout que si tu fais des cases à cocher, le choix 3 revient juste à cocher les 2 premières, et le choix 5 revient juste à cocher les 3 cases, ce ne sont pas vraiment des choix en soit... 4) Sinon on peut archiver une version intacte automatiquement normalement, VBA gère la sauvegarde de fichiers, il suffit de faire cette opération en début de macro Et pour la suite c'est tout bon normalement Par contre, on est d'accord, les feuilles que l'on veut alléger, on s'en débarasse une fois qu'on a créé une copie parfaite allégée? Ah moins que tu ne souhaites vérifier que tout se soit bien passé.....

Ausecour · Answer

J'apporte une information par rapport au dernier point que tu as souligné, je craignais justement que tu aies des références à ces cellules, mais il y a une parade, quand tu coupes et colles des cellules, les formules qui y font référence suivent au niveau des formules, Excel comprend que tu déplaces ces cellules...

Ausecour · Answer

Désolé pour le temps que je mets à te répondre, j'étais plutôt occupé cette semaine... le nom des fonctions pour couper et coller c'est simplement Cut et Copy normalement, tu utiliseras peut-être aussi CutCopyMode = false si besoin Tu souhaites toi même avancer sur ce projet ou bien que je m'en charge...