Doublons et Suppression onglets sous condition

Slt le Forum,

Je me retourne vers vous car impossible de trouver ce que je cherche ou qui s'en rapproche suffisamment pour pouvoir l'adapter mes besoins.

Deux choses :

La première consiste à isoler ou ne garder que les lignes "Orphelines" ce qui suppose un repérages des lignes dont les cellules en colonne A et B contiennent les mêmes choses et de les supprimer toutes 2 et non pas qu'une des 2 comme cela est proposé sur tous les sujets traitant de la gestion des doublons. Ceci sur la feuille de travaille en cours.

La deuxième consiste à supprimer tous les onglets (d'un classeur ouvert) dont au moins une des 150 1res cellules de la colonne B ne contiendrait pas telle date ou telle date ou telle date sachant que la date au format 19/10/2014 se trouve en fin de cellule :

Blabla blablabla - 19/10/2014

Merci pour votre aide

Bonjour Fouggy, bonjour le forum,

Pour ton second problème regarde le fichier en pièce jointe. Dans le premier onglet, le bouton Dates de Référence va lancer une UserForm te permettant d'indiquer les dates de références. Tu en ajoutes autant que tu veux avec le bouton Ajouter et quand tu as fini, clique sur le bouton Valider. La procédure Macro1 du module Module1 prend le relai...

Code commenté.

Pour l'exemple il te faut entrer dans l'UserForm les 3 dates en colonne G du premier onglet. Un message de demandera d'accepter la suppression du premier onglet (à cause de la cellule B5) et du troisième onglet (à cause de la cellule B4).

Pour ton premier problème, je vais y jetter un œil...

Bonjour Fouggy, bonjour le forum,

En pièce jointe un fichier pour ton premier problème avec le code ci-dessous :

Sub Macro1()
Dim O As Object 'déclare la variable O (Onglet)
Dim DL As Integer 'déclare la variable DL (Dernière Ligne)
Dim TC As Variant 'déclare la variable TC (Tableau de Cellules)
Dim D As Object 'déclare la variable D (Dictionnaire)
Dim I As Integer 'déclare la variable I (Incrément)
Dim AetB As String 'déclare la variable AetB (Concaténation des cellules des Colonnes A et B)
Dim TOU As Variant 'déclare la variable TOU (Tableau de Occurrences Uniques)
Dim TNO As Variant 'déclare la variable TNO (Tableau du Nombre d'Occurrences)
Dim PL As Range 'déclare la variable PL (PLage)
Dim PLV As Range 'déclare la variable PLV (PLage Visible)

Application.ScreenUpdating = False 'masque les rafraîchiseements d'écran
Set O = Sheets("Feuil1") 'définit l'onglet O (à adapter à ton cas)
'définit la dernière ligne DL (la plus grande valeur entre la colonne A et la colonne B)
DL = Application.WorksheetFunction.Max(O.Cells(Application.Rows.Count, 1).End(xlUp).Row, O.Cells(Application.Rows.Count, 2).End(xlUp).Row)
TC = Range("A2:B" & DL) 'définit le tableau de cellules TC
Set D = CreateObject("Scripting.Dictionary") 'définit le dictionnaire TC
For I = 1 To UBound(TC) 'boucle sur toutes les cellules de la plus grande colonne du tableau TC
    AetB = TC(I, 1) & "XXX" & TC(I, 2) 'définit la variable AetB (concaténation de la valeur de A + [XXX] + valeur de B)
    D(AetB) = D(AetB) + 1 'alimente le dictionnaire
Next I 'prochaine cellule de la boucle
TOU = D.keys 'récupère dans le tableau des occurrences unique TOU la liste du dictionnaire D sans doublons
TNO = D.items 'récupère dans le tableau des nombres d'occurrences TNO le nombre d'occurences, pour chaque occurrence unique
Set PL = O.Range("A2:A" & DL) 'féfinit la plage PL
For I = 0 To UBound(TNO) 'boucle sur tous les éléments du tableau TNO
    If TNO(I) > 1 Then 'condition : si il y a plus d'une seule occurence
        'filtre la colonne A de l'onglet O avec la partie avant l'espace de l'occurrence correspondante comme critère
        O.Range("A1").AutoFilter Field:=1, Criteria1:=Split(TOU(I), "XXX")(0)
        'filtre la colonne B de l'onglet O avec la partie après l'espace de l'occurrence correspondante comme critère
        O.Range("A1").AutoFilter Field:=2, Criteria1:=Split(TOU(I), "XXX")(1)
        Set PLV = PL.SpecialCells(xlCellTypeVisible) 'définit la plage PLV (plage visible (non filtrée) de la plage PL)
        PLV.EntireRow.Delete 'supprime les lignes entières de la plage PLV
        O.Range("A1").AutoFilter 'supprime le filtre automatique
    End If 'fin de la condition
Next I 'prochain élément de la boucle
Application.ScreenUpdating = False 'affiche les rafraîchiseements d'écran
End Sub

Il faut que les données contiennent des étiquettes (voir oglet Feuil1, cellule A1 et A2, Donnée1 et Donnée2) pour le que le filtre automatique fonctionne correctement.

Le fichier :

Slt ThauThème et merci pour ta réponse rapide et tes macros qui ne manquent pas de complexité à mon niveau.

Je suis sur la macro relative aux dates (second problème) et ça n'a pas l'air de fonctionner.

J'ai procédé de la sorte :

1)- Ouverture de ton fichier et inscription de la date souhaitée ou des dates souhaitées en colonne G

2)- Clique sur le bouton dates de références et entrée des mêmes dates inscrites en colonne G

3)- Ajouter à chaque fois puis valider

Lorsque j'ouvre mon fichier à traiter en appliquant la macro 1 . Il m'envoie "erreur d'exécution9". L'indice n'appartient pas à la sélection.

Et même en copiant ta macro dans le module de mon fichier à traiter, ça le fait pas.

Peut-être que je me suis mal exprimé dans mon exposé du problème ?

La colonne B ne contient pas une série de dates mais un certain nombre d'infos comme dans les autres colonnes d'ailleurs sauf qu' une des cellules, pas forcément placée su la même ligne, de cette colonne B, contient une info avec une date comme expliqué plus haut : (Blablabla bla - date), d'autres cellules de cette même colonnes contiennent des infos sans dates et d'autres en core sont vides.

Mes classeurs à traiter peuvent contenir jusqu'à 15000 onglets et les infos contenues dans chaque onglet ne dépassent pas la ligne 150 au-delà de laquelle il n'y a plus d'infos.

Le but de la manoeuvre est de localiser ou extraire les onglets (parmi 15000) dont une des cellules de la colonne B contient la date qui m'intéresse, ceci afin de leur appliquer d'autres macros que j'ai pu réaliser. Du coup l'idée de supprimer tous les autres onglets ne me paraît pas la plus rapide des solutions non plus.

La macro à réaliser et dont la difficulté dépasse mon niveau actuel ressemblerait à quelque chose comme une macro à coller dans le module du fichier à traiter et qui après avoir été lancée ferait :

1)- Demande d'abord le ou les dates à sélectionner

Date(s) à entrer et à valider avec validation qui envoie la suite de la macro :

2)- Inspecte chaque cellule de la colonne B (en partant du haut jusqu'à 150 pour limiter la recherche) de chaque onglet jusqu'à trouver cette fameuse "cellule datée" pour la comparer aux cellules sélectionnées.

3)- Si la date de la cellule trouvée en colonne B correspond à une des dates sélectionnées conserve l'onglet sinon on supprime le du classeur.

Ceci prendrait énormément de temps au final je crois pour supprimer tous les autres onglets et obligerait à renommer le classeur afin de ne pas perdre celui d'origine avec toutes les info réexploitables.

Du coup ça ressemblerait pluto (comme le chien de Mickey, mdrrrrrrrrrrrr) à :

1)- Demande d'abord le ou les dates à sélectionner

Date(s) à entrer et à valider avec validation qui envoie la suite de la macro :

2)- Inspecte chaque cellule de la colonne B (en partant du haut jusqu'à 150 pour limiter la recherche) de chaque onglet jusqu'à trouver cette fameuse "cellule datée" pour la comparer aux cellules sélectionnées.

3)- Si la date de la cellule trouvée en colonne B correspond à une des dates sélectionnées déplace la en début de classeur.

Dans l'attente je me penche sur ton autre macro et en tous les cas merci encore.

Oupsssssssssssssss

Pour être plus clair encore :

1)- Demande d'abord le ou les dates à sélectionner

Date(s) à entrer et à valider avec validation qui envoie la suite de la macro :

2)- Inspecte chaque cellule de la colonne B (en partant du haut jusqu'à 150 pour limiter la recherche) de chaque onglet jusqu'à trouver cette fameuse "cellule datée" pour la comparer aux cellules sélectionnées.

3)- Si la date de la cellule trouvée en colonne B de l'onglet en cours correspond à une des dates sélectionnées déplace l'onglet en début de classeur sinon passe à l'autre et ainsi de suite jusqu'au dernier onglet du classeur.

Bonsoir Fouggy, bonsoir le forum,

Pour le second problème, la procédure que tu as utilisée ne peux pas fonctionner dans ton fichier. Voila ce que je te propose. Pour éviter de tourner en rond, envoie-moi un fichier exemple (avec le même nom que ton fichier original) avec juste juste 4 ou 5 onglets (15000 onglets dans un même classeur je navet jamais vu ça ! C'est fou...)

As-tu regardé la solution proposée pour ton premier problème ?

Slt Thauthème et le forum,

J'aimerais que tu me dises, sur le principe même, pourquoi cela ne peut pas marcher, cela me fera avancer.

Je te prépare le fichier demandé et l'envoie dans mon prochain post.

Hé ouiiii, 15000 onglets et même un de 17000. Au-delà ma bécane serre, lol. Et 38 fichiers à traiter (15 macros successives) comme cela, lol. Tout va bien.

En attendant je t'envoie mon fichier Test pour les orphelins où il y a quelques soucis aussi. Il y a déjà un pb rencontré souvent, les 2 1res lignes n'étant pas prises en compte et il doit s'agir de l'option "ligne d'entête ou non".

Par suite et malgré la grosseur du fichier le test est facile car après application de ta macro, il suffit d'appliquer l'option doublons d'excel pour voir afficher les résultats.

Quelques mots sur ce fichier et le besoin de cette macro "orphelins". Ayant appliquer plusieurs macros successives sur un nombre de données conséquentes comme tu as pu t'en apercevoir, il peut se produire que des erreurs de traitement surviennent (macro pas compatible ou erreur de manip). Ayant 2 types de fichiers dont les 2 première colonnes contiennent les mêmes types de valeurs (tout le reste étant différent) je veux me servir de l'un pour vérifier l'autre et vice versa en copiant les 2 1res colonnes A et B de l'un à la suite des colonnes A et B dans l'autre. Après un tri des colonnes A et B, je ne dois avoir logiquement que des doublons confirmant qu'il y a autant de lignes de données dans l'un que dans l'autre. S'il existe des orphelins c'est que ma copie est à revoir. dans le fichier que je t'envoie, les lignes vertes correspondent à celles d'un des 2 fichier et les autres, celles de l'autre. Tout est déjà trier, prêt à être traiter avec ta macro. Envoie ensuite la recherche de doublons excel et tu feras le même constat que moi.

Courage

A très bientôt

Et merci encore de l'aide que tu essaies de m'apporter.

Bonjour Fouggy, bonjour le forum,

Je te demandais un fichier de ton problème nº 2 pour justement voir et t'expliquer où se situais le problème. En effet, tu me disais : Il m'envoie "erreur d'exécution9". L'indice n'appartient pas à la sélection.

Mais tu ne me disait pas à quelle ligne il te renvoyait cette erreur. J'ai ma petite idée mais je ne suis pas devin. Alors fait l'effort de m'envoyer ce fichier comme je te le demande et on pourra avancer.

Je regarde celui-ci pour ton problème nº 1... mais si tu relis, je te disais : Il faut que les données contiennent des étiquettes (voir oglet Feuil1, cellule A1 et A2, Donnée1 et Donnée2) pour le que le filtre automatique fonctionne correctement.

[Édition]

Avec les étiquettes ça à l'air de marcher... Macro effectuée en 16 secondes et des poussières... Voir pièce jointe

Slt Thauthème, Slt le Forum,

Ca y est, ta macro "Orphelin" marche au top sauf le traitement des 2 1res lignes mais cela n'est pas bien grave, suffit de le savoir, lol.

Pour la macro de recherche de dates je t'envoie un exemple de fichier à traiter. Mon problème c'est que même en retournant jeter un oeil sur les cours VBA j'ai du mal à comprendre le fonctionnement d'un Userform et l'utilisation des étiquettes.

@+++ et grand merci encore

Bonjour le fil, bonjour le forum,

On peut supprimer l'UserForm mais alors comment indiquerais-tu les dates de référence ?

Dans l'attente de ta réponse...

Slt à tous,

Ben écoute, je ne sais pas. Peut-être pas besoin de le supprimer s'il est vraiment nécessaire et si t'arrives à me donner la marche à suivre pas à pas. J'aurais encore appris quelque chose.

Il y a quelques années encore je ne savais même pas comment fonctionnait une macro. Je ne savais pas comment y accéder et encore moins utiliser les fonctions de base...

La commande alt/ F11, je l'ai découverte début août quand je me suis persuadé qu'il fallait bosser des cours pour arriver à un minimum d'autonomie. J'avance à mon rythme et suis désoler de passer encore pour un bourrin mais Rome ne s'est pas fait en 1 jour...

Cette macro est importante pour moi car, pour t'en dire un peu plus, j'ai traité, avec des macros dont certaines me sont propres (résultat de ma progression tout de même), 2 types d'informations réparties en 2 fois 19 fichiers de 5000 à 17000 onglets. Ces 2 types d'informations ont 2 séries d'info communes et datées regroupées en colonnes A et B.

La recherches d'"Orphelins" m'indique si les 2 types d'informations ont été traitées correctement d'un coté comme de l'autre. S'il n'y en a pas c'est que tout va bien à moins d'avoir produit les mêmes erreurs de part et d'autre et sur des onglets contenant les mêmes dates,, chose impensable car les séries de macros appliquées (17 d'un et 19 de l'autre) de part et d'autre ne sont pas les mêmes.

En revanche et comme c'est le cas, la présence d'"Orphelins" que mes macros ont merdé quelque part et notamment sur les onglets contenant les dates restant orphelines.

Par suite il faut que j'aille rechercher les onglets concernés dans les séries crées avant traitement des macros initiales pour analyser où cela a bien pu merder. Le nom des onglets n'indiquant en rien la date qu'ils contiennent et chacun des 2 type d'information contenant environ 80000 onglets répartis en 19 fichiers... Je te laisse imaginer l'ampleur de la tâche si je ne dispose pas d'une macro (dont j'ai un réel besoin et que je suis parfaitement incapable de construire aujourd'hui à mon niveau) m'indiquant précisément les onglets recherchés.

Voilà donc. Tu sais tout. Donc à toi de voir ce qu'il est possible de faire.

Je me dis quand-même que même avec un UserForm, si on m'indique la démarche précise pas à pas, un peu à la façon d'une hot-line s'adressant à des personnes qui n'y connaissent absolument rien de chez rien en informatique, cela devrait le faire.

Cela me permettrait par ailleurs de me familiariser de manière pratique avec des notions dont je n'ai pas la maîtrise actuellement.

Un grand merci encore pour tout le temps que tu as déjà passé sur ma problématique.

Bonsoir Fouggy, bonsoir le forum,

En pièce jointe le fichier qui m'a l'air de tourner convenablement. À tester avec des fichiers exemple avant d'utiliser les fichiers de travail. Ou, penser à enregistrer-sous pour garder l'original au cas où...

Comme tu as 38 fichiers ça va être un peu pénible mais la procédure est relativement simple (à faire pour chaque fichier) :

• ouvre les deux fichiers. Celui en pièce jointe Fouggy_v04.xlsm et ton fichier de travail

• [Alt]+[F11] pour ouvrir l'éditeur Visual Basic (VBE)

• tu y distingues les projets VBA des deux fichiers

• fais glisser le composant UserForm1 du projet VBA VBAPrject(Fouggy_v04.xlsm dans le projet VBA de ton second fichier. Peut importe l'endroit, quand tu lâches le bouton de la souris il se positionnera au bon endroit...

• fais glisser de la même manière le module ModuleRobert dans le projet VBA de ton second fichier

• ferme VBE ([Alt]+[F11))

• ferme le classeur Fouggy_v04.xlsm

• ouvre la boîte de dialogue Macro ([Alt]+[F8])

• dans le champ Macros dans, vérifie que se soit bien Ce classeur

• sélectionne la macro Ouverture_USF et clique sur le bouton Exécuter ou double-clique sur la macro Ouverture_USF

Là tu dois retrouver ce que je t'avais déjà envoyé :

• la boîte de dialogues Dates de Référence s'ouvre

• tu entres une date et tu cliques sur le bouton Ajouter (ou la touche Entrée)

• Quand tu as rentré toutes les dates que tu désires, tu clique sur le bouton Valider ou ([Alt]+[V])

• un message te demandera de confirmer la suppression (un par un) des onglets ne contenant pas une des dates entrées

Si le message t'emm... on le supprimera mais il vaut mieux que tu fasses tourner d'abord pour vérifier la fiabilité du code...

Le fichier :

Slt Thauthème et le forum,

Pas le temps de tester ta "démarche" dès ce soir car j'ai du monde à la maison mais suis venu faire un tour sur ma messagerie en attente d'une réponse et... je l'ai.

Yooooooooooooo...

Donc envie de répondre tout de suite parce que j'ai envie de t'adresser d'énormes merci, pour ton investissement dans ma demande tout d'abord mais aussi et surtout pour l'adaptation de ta réponse à mon niveau.

Je vais prendre le temps, dès demain de respecter ta démarche, pas à pas, à tête reposée pour n'en rater aucune et te ferais part des résultats obtenus qui, je n'en doute pas devraient être au rendez-vous.

Si c'est pas le cas, je t'en ferais part aussi.

Quoi qu'il arrive je tiens à exprimer le fait que ta démarche d'aide et d'adaptation de ta ou tes réponse(s) frôle l'exceptionnel et que certains se plaignent parfois d'apporter une réponse sans aucun merci en retour , ce qui peut-être légitimement frustrant.

Entre les demandes "clès en main" et les réponses, certains en oubli parfois l'essentiel : l'aide et le minimum de reconnaissance pour ceux qui font l'effort de prendre le temps de se pencher sur une problématique.

Je tiens donc, par ce message, à reconnaître toutes les réponses tendant à aider ceux qui ont des besoins et féliciter l'ensemble du forum à ce titre et toi en particulier pour cette dernière démarche très particulière.

Quand on est satisfait d'une réponse ou d'une démarche il faut savoir le dire et prendre le temps de le dire.

C'est l'unique sens de ma présente réponse de ce soir.

Pour les résultats du test, je te dis tout cela dès demain.

Bonne nuit à toi et à tous.

Slt Th et le Forum,

Je viens de tout exécuter pas à pas de manière très scrupuleuse et ça bloque :

1)- J'ai ouvert mon fichier à tester

2)- J'ai ouvert le tiens

3)- J'ai supprimé ce qui se trouvait à l'intérieur du module 1 de mon fichier qui est une macro qui a été appliquée. Elle doit figurer d'ailleurs encore dans le fichier test que je t'ai envoyé. Ceci afin qu'il ne puisse y avoir d'interférence éventuelle.

4)- J'ai copier/coller les 2 macros de ton fichier dans mon module 1

5)- J'ai lancer

Il annonce : Erreur d'exécution 424 - Objet Requis

Ligne Userform1.show

(ne pas t'annoncer la ligne incriminée comme la dernière fois est impardonnable à mon niveau mais il s'agissait simplement d'un oubli que je ne reproduirais pas)

Ne connaissant pas encore toute les subtilités du VBA, je suis revenu sur ton fichier pour constater que tes 2 macros avaient été mises chacune dans un module différent et que l'erreur pouvait provenir de cela.

Je suis donc revenu sur mon module1 pour dissocier les 2 macros en créant un 2me module pour n'y copier que la partie Userform1 et supprimer cette dernière partie du module1 (représentation exacte de ton fichier).

Et là, le résultat est le même : Même annonce d'erreur et même ligne.

Je pense que tu dois avoir ton idée.

Dans l'attente de tes news.

Fouggy

Bonsoir Fouggy, bonsoir le forum,

Non, tu n'as pas suivi scrupuleusement la procédure... Je ne t'ai pas demandé de copier les macro dans ton Module1 mais de copier le module entier. Idem pour l'UserForm. Il te faut le copier aussi dans ton fichier en suivant la procédure ci-dessous !

• fais glisser le composant UserForm1 du projet VBA VBAProject (Fouggy_v04.xlsm dans le projet VBA de ton second fichier. Peut importe l'endroit, quand tu lâches le bouton de la souris il se positionnera au bon endroit...

• fais glisser de la même manière le module ModuleRobert dans le projet VBA de ton second fichier

Après on en reparle...

Yooooooooooooo,

Je savais même pas qu'on pouvant faire glisser donc j'apprends encore, lol. La différence entre le copier/coller d'1 module à l'autre je le sais pas et cela n'ai pas expliqué dans les cours VBA du présent site à moins que j'ai sauté quelque chose encore. Mais cela fait partie des finesses qu'il me reste à acquérir.

J'ai procédé comme suit :

1)- Epuration de mon 1er fichier test comportant plus de 200 onglets (Fichier 1 : Test Thauthème Date 01)

2)- Glissement comme rappelé dans ton dernier post pour vérification si cette manip a bien été effectuée (Fichier 2 : Test Thauthème Date 02)

3)- Application de l'USF avec date entrée 01/01/2009 dont il existe au moins 4 onglets(voir Fichier1), suis pas aller compter plus. (Fichier 3 : Test Thauthème Date 03)

Dans ce dernier fichier, il ne reste qu'un onglet daté du 05/01/2009 !

Pas de message d'erreur mais les suppressions successives ayant été faites à la main je le rate peut-être à chaque fois. J'abouti en tous les cas, à chaque test, à la ligne surlignée jaune de la macro :

CL.Sheets(Z).Delete 'supprime l'onglet

J'ai l'impression qu'on avance mais qu'il reste quelques détails à régler.

Si c'est encore une mauvaise manip de ma part avec tout ce que tu m'as fourni comme explication, avec mes fichiers joints sur ce post contenant les manip effectuées, je t'autorise à me déclarer "1er Boulet de ce forum", lol

Bah, du coup je sais pas. Je reste confiant en ce qui te concerne mais je commence à douter de moi et des quelques misérables bases que je pensais posséder.

Dans l'attente de tes news.

Cordialement.

Bonsoir Fouggy, bonsoir le forum,

Je crois qu'on tient le bon bout... J'ai pas mal remanié le code et je pense qu'il devrait convenir. Fait de nombreux essais avant de le considérer comme bon...

Je n'ai pas touché à l'UserForm, juste aux macros du module ModuleRobert. Mais, autant faire glisser les deux dans les fichiers de travail.

Attention j'ai supprimé le message d'avertissement de suppression d'onglet ! Du coup ça tourne à 2000 à l'heure...

Le fichier :