Powerquery ? VBA ? "Gestionnaire de noms" non accessible

Bonjour à tous,

J'espère que vous allez bien !

Je rencontre un souci, j'ai un fichier du taff qui a été malmené pendant des années. Ce fichier a été créé en 2003 et pleins de gens l'ont modifié/fait évoluer/détruit/reconstruit/et pour finir ont sûrement fait n'importe quoi avec. Bref là n'est pas le sujet.

Le sujet est lié au fait que le gestionnaire de noms a accumulé depuis 2003 je ne sais combien de référence nommées au point où je ne peux plus avoir accès au gestionnaire de nom quand je clique sur "Formules" puis "Gestionnaire de noms". Ca charge dans le vide pendant 10 secondes et rien ne se passe.

J'ai donc fait plusieurs choses :

- J'ai enté d'utiliser VBA pour virer toutes ces plages nommées => Résultat : échec, mon pc n'a pas assez de mémoire.

- J'ai tenté d'utiliser PowerQuery, avec l'instruction : = Excel.CurrentWorkbook() qui devrait afficher l'ensemble des éléments nommés du classeur mais à part tourner dans le vide il ne s'est rien passé.

- J'ai tenté d'utiliser Copilot mais ça me ramenait à la méthode VBA.

Est ce que quelqu'un aurait une idée de génie ? Et je sais que ce forum en regorge donc n'hésitez pas avec vos propositions

@+

Hello,

Combien de "Name" via "Names.Count"

S'il y en a trop, les supprimer par poignée?

Hello !

Mon sauveur voici le nombre :

Bonjour à tous !

Une copie d'un onglet vers une destination nouvelle, donc "propre", transportera peut être, les plages nommées valides ?!

Hello JFL,

Je me doutais que cette proposition arriverait. Lorsque je fais déplacer/copier vers un nouvel onglet, Excel décède

Nice...

J'en avais déjà vu à quelques milliers, mais là...

L'idée de faire par poignées :

Sub delete_nms()
Dim I As Integer
For I = 5000 To 1 Step -1
   Names(I).Delete
Next I
End Sub

A renouveler autant de fois que nécessaire...

Attention, tous les noms seront supprimés...

Bonjour à tous de nouveau !

Alors je vais de ce pas brûler un cierge....

Courage !

Re-,

Pour le déplacement des données, il ne faut pas déplacer-copier l'onglet (les noms suivent), mais un beau Ctrl + C des plages

Peut-être?

Bonjour à tous,

Si tu ne veux pas supprimer tous les noms "sans vérifier", tu pourrais faire une boucle pour les exporter dans un tableau (nom/valeur) et ensuite tu bosses dans Excel directement.

Tu pourrais faire une 3e colonne de "a supprimer : vrai/faux", et tu y mets les #REF, etc. Puis via VBA tu lis le tableau et tu supprimes les VRAI.

(il faudrait relancer la macro de cousinhub 31 fois pour supprimer tous les noms d'un coup, autant boucler de Names.Count à 1 dans ce cas).

Hello Saboh,

Justement j'ai des erreurs de capacités quand je fais ça... C'était mon idée de départ

Ca a l'air de fonctionner via la méthode de notre breton préféré par bloc de 5000 donc j'en ai à peu près pour 3h. Je reviens vers vous messieurs et merci pour la réactivité à tous

@+

Ah oui d'accord. Attention met un On Error Resume Next car si tu as des tableaux ça risque de planter.

(+ calculation manual, etc etc).

Perso j'aurai testé avec un DoEvents tous les x noms supprimés (par exemple 5000 si ça fonctionne), j'avoue que j'ai du mal à conceptualiser pourquoi surgit cette erreur de mémoire (j'ai vu sur internet que c'est normal avec autant de noms) puisqu'on supprime 1 par 1…

Bon courage en tout cas 💀

Bonjour à tous de nouveau !

BAROUTE78 a écrit :

Ca a l'air de fonctionner via la méthode de notre breton préféré par bloc de 5000 donc j'en ai à peu près pour 3h. Je reviens vers vous messieurs et merci pour la réactivité à tous

Une tentative d'accéder au gestionnaire de noms entre chaque lancement de la macro pourra, peut-être, se révéler salutaire...

Re-,

Un petit garde-fou (s'il n'est pas trop tard....)

Sub del_nms2()
Dim I As Integer
For I = 5000 To 1 Step -1
    If Not Names(I).ValidWorkbookParameter Then Names(I).Delete
Next I
End Sub

Sinon, à demain

Hello cousinhub, JFL, Saboh,

Après un petit temps et à coup de suppression par 10 000 on y est arrivé. J'ai juste un souci. Quand j'utilise la propriété Names.Count, il me renvoie 3 sauf que dans le gestionnaire de nom je n'ai rien. Vous savez d'où ça peut venir ?

@+

Salut,

Probablement masqués : Name.Visible property (Excel) | Microsoft Learn (§ remarks)

Re-,

BAROUTE78 a écrit :

il me renvoie 3 sauf que dans le gestionnaire de nom je n'ai rien.

Oserais-je???

PQ donne quoi?

Hello cousinhub, Saboh,

Merci encore, en effet avec ce petit bout de code j'ai résolu mon souci des trois derniers non visibles

Sub RendreTousLesNomsVisibles()
    Dim nm As Name
    For Each nm In ThisWorkbook.Names
        nm.Visible = True
    Next nm
End Sub

Encore merci pour votre aide, une fois de plus vous êtes au top

@+

Bonjour à tous de nouveau !

C'est avec satisfaction que j'observe que mon expertise en VBA a été mise à profit de manière efficace.