Recherche numéros de lignes puis les supprimer

Le 05/01/2023 à 10:28

Bonjour à tous,

Premièrement je vous souhaite une très bonne année 2023.

Je reste bloqué depuis un certain temps sur mon besoin.

Le voici.

J'ai une feuille dans laquelle j'ai des données en lignes sur plusieurs colonnes., la ligne 1 étant les en-têtes.

Ces données sont triées par ordre chronologique de la colonne A qui sont des dates.

Je souhaite, par l'intermédiaire de 2 Message Box, définir une plage de dates que je souhaite conserver toutes les lignes en dehors de cette période devant être supprimées.

Je voulais passer par une fonction qui me permettrait de:

- Récupérer le numéro de ligne de la première occurrence de la date de début de la plage définie que l'on peut nommer "ligne_debut"

- Récupérer le dernier numéro de ligne de la dernière occurrence de la date de fin de la plage définie que l'on peut nommer "ligne_fin"

Puis ensuite, en utilisant ces numéros de lignes pour:

- Supprimer les lignes comprises entre "ligne_fin"+1 et la ligne 200000

- Supprimer les lignes entre la ligne 2 et la "ligne_debut"-1

J'espère que je suis clair.

En vous remerciant par avance.

Cdlt

Le 05/01/2023 à 11:15

Je vais imager un peu mon problème.

Et je joins le fichier Excel (très simplifié) si besoin.

Merci par avance.

image
11exemple-suppression-lignes.xlsx (10.95 Ko)
Le 05/01/2023 à 11:24

Bonjour à tous !

Et.....

Pouvez-vous préciser, dans votre profil, la nature de votre Excel (2007 ? 2013 ? 2019 ? 365 ? etc.... la langue utilisée étant une information très mineure) ?

Le 05/01/2023 à 11:25

Bonjour,

A tester

12exemple-suppression-lignes.xlsm (17.04 Ko)
Le 05/01/2023 à 12:06

@M12

Cela ne fonctionne pas.

Voici le code que j'ai commencé à écrire qui me permet d'importer les données d'un autre fichier, puis de trier mes données par date.

Ensuite mes 2 textbox pour définir la plage que je souhaite conserver (Date_debut et Date_fin).

Il faudrait que je puisse identifier les lignes après Date_fin (232 par exemple) jusqu'à la ligne 200000 (nombre max de lignes du fichier) et faire une fonction

Rows("232:200000").delete

Idem pour les lignes avant Date_début (121 par exemple) et donc depuis la ligne 2 qui est la première ligne de données du tableau, et faire une fonction

Rows("2:121").delete

J'espère que je suis clair.

Sub Import_Pointages()

Dim Plage As Range
Dim Cellule As Range
Set Plage = Sheets("Synthèse pointages").Range("A2:A200000")

'Effacer les données de l'onglet synthèse avant l'import
Sheets("Synthèse pointages").Select
Range("A2:X200000").Select
Selection.ClearContents
Range("A2").Select

'Ouvre le fichier de saisie des pointages qui doit être dans le même dossier /!\ le nom du fichier ne doit pas être modifié /!\
Workbooks.Open Filename:=ThisWorkbook.Path & "\Fichier de pointages.xlsm"
Windows("Fichier de pointages.xlsm").Activate
Sheets("Synthèse pointages").Select
Range("A2:N200000").Select
Selection.Copy
Windows("Fichier indicateurs pointages.xlsm").Activate
Sheets("Synthèse pointages").Select
Range("A2").Select
ActiveSheet.Paste
Windows("Fichier de pointages.xlsm").Activate
Application.CutCopyMode = False
Application.DisplayAlerts = False
ActiveWorkbook.Close
Windows("Fichier indicateurs pointages.xlsm").Activate
Range("A2").Select

Range("A2:M200000").Select
ActiveWorkbook.Worksheets("Synthèse pointages").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Synthèse pointages").Sort.SortFields.Add Key:=Range("A2"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Synthèse pointages").Sort
.SetRange Range("A2:M200000")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A2").Select

'Selectionne des dates à conserver
Date_debut = InputBox("Saisir la date de DEBUT ", "Format JJ/MM/AAAA")
Date_fin = InputBox("Saisir la date de FIN", "Format JJ/MM/AAAA")

Fin du code à écrire...

End Sub
Le 05/01/2023 à 12:20

Bonjour,

Et si au lieu d'importer toutes tes données par le biais de VBA, tu les importais via Power Query?

La sélection de la plage temporelle serait automatisée (en mettant les dates dans 2 cellules prédestinées), le tri automatique et fait dans la requête, et juste les données nécessaires seraient importées... (et il serait inutile d'ouvrir le fichier d'export...)

@ voir...

Le 05/01/2023 à 12:25

Bonjour à tous !

Avec Excel 2016, je ne peux que plussoyer la piste proposée par cousinhub ().

Simplicité de mise en œuvre, robustesse,..... le bonheur est simple comme une requête Power Query

Le 05/01/2023 à 15:12

Je ne peux utiliser la requête Power Query car les personnes destinées à utiliser le fichier sont des utilisateurs très basique d'Excel.

En gros il faut juste cliquer sur un bouton, et répondre aux 2 questions de dates mais pas plus.

En plus derrière j'ai tout un tas de calcul sur un autre onglet qui reprend les données de cette feuille.

Mais merci pour la proposition!! ;)

Je reste donc intéressé pour avoir des propositions pour finir mon code.

Merci

Le 05/01/2023 à 15:16

Re-,

Pas de soucis...

Cependant, mettre deux dates dans 2 cellules, et un clic droit pour actualiser, je ne vois que du basique moins-moins

Et de plus, disposer d'un Tableau Structuré pour toutes tes formules ne peut que renforcer ton fichier...

Bon courage pour ton code

Le 05/01/2023 à 15:23

Il y a surement une subtilité que je ne connais pas dans la requête Power Query, car pour moi il faut retourner dans la requête pour modifier les 2 dates de tri.

Si je peux indiquer les dates dans 2 cellules de mon fichier avant de lancer la requête alors oui cela correspond tout à fait à mon besoin.

Le 05/01/2023 à 15:32

Re-,

C'est ce que je te disais lors de ma proposition initiale :

La sélection de la plage temporelle serait automatisée (en mettant les dates dans 2 cellules prédestinées),

Ou dans la 2ème :

mettre deux dates dans 2 cellules, et un clic droit pour actualiser,

Cependant, pour pouvoir t'aider au mieux, il serait bon que tu nous donnes les structures exactes de tes 2 fichiers :

- Onglet "Synthèse pointages" du fichier "Fichier de pointages.xlsm"

- Onglet "Synthèse pointages" du fichier "Fichier indicateurs pointages.xlsm"

En mettant deux fichiers exemples, exemptes de toutes données confidentielles, mais respectant les structures

Et comme les deux fichiers sont dans le même répertoire, il y aura très peu (voire pas) de modifications à faire par tes soins pour l'adapter à ton environnement.

@ te relire si cela t'intéresse

PS, si tu le désires, on pourra également t'aider pour adapter tes formules de traitement

Le 05/01/2023 à 15:50

Ok banco.

Voici mes 2 fichiers.

Le fichier "Fichier indicateurs pointages" va chercher ses infos dans le fichier "Fichier de pointages" et l'onglet "Synthèse pointages".

Ensuite mon onglet "Indicateurs" du fichier "Fichier indicateurs pointages" fait des calculs avec des SOMMEPROD basés sur l'onglet de l'import.

Les dates de définition de la période à importer sont en cellules D9 e D13 de l'onglet "Indicateurs" du fichier "Fichier indicateurs pointages".

7fichier-de-pointages.zip (713.88 Ko)
17fichier-indicateurs-pointages.xlsm (107.57 Ko)
Le 05/01/2023 à 19:42

Re-,

Zed69 a écrit :

Ok banco.

Ben, tu sais quoi, c'est toi qui va le faire...

J'ai vu dans le fichier "indicateur", que tu avais déjà fait une requête, et qu'il ne te manquait que les critères pour choisir les dates en "auto"...

Donc, dans l'onglet "Indicateurs", tu nommes les cellules "D9" en "T_Deb" et "D13" en "T_Fin" (via le gestionnaire de noms, ou directement, en sélectionnant la cellule, et en tapant le nom dans la case en haut à gauche :

image

Et idem pour D13

Ensuite, lorsque tu ouvres l'éditeur PQ, dans le ruban Accueil, tu as "Éditeur Avancé"

image

Le code que t'avait généré PQ lors de ton essai donnait ceci :

let
    Source = Excel.Workbook(File.Contents("C:\Users\meidhi.haliti\Desktop\Dossier Travail\DERVASIL\Fichier de pointages.xlsm"), null, true),
    #"Synthèse pointages_Sheet" = Source{[Item="Synthèse pointages",Kind="Sheet"]}[Data],
    #"En-têtes promus" = Table.PromoteHeaders(#"Synthèse pointages_Sheet", [PromoteAllScalars=true]),
    #"Type modifié" = Table.TransformColumnTypes(#"En-têtes promus",{{"Date", type date}, {"Matricule", Int64.Type}, {"OF", type text}, {"Article", type text}, {"N° machine", type text}, {"Qté conformes", Int64.Type}, {"Qté rebus", Int64.Type}, {"Qté épluchés", type any}, {"Temps (ch)", Int64.Type}, {"Observation", type any}, {"Temps TOTAL", Int64.Type}, {"Temps pièces OK", Int64.Type}, {"Temps pièces NOK", Int64.Type}}),
    #"Lignes triées" = Table.Sort(#"Type modifié",{{"Date", Order.Ascending}}),
    #"Lignes filtrées" = Table.SelectRows(#"Lignes triées", each [Date] >= #date(2023, 1, 1) and [Date] <= #date(2023, 1, 2))
in
    #"Lignes filtrées"

Afin de rendre automatique les dates de filtre, on va rajouter 2 variables (T_Deb et T_Fin), et modifier le code de filtre comme ceci :

let
    Source = Excel.Workbook(File.Contents("C:\Users\meidhi.haliti\Desktop\Dossier Travail\DERVASIL\Fichier de pointages.xlsm"), null, true),
    #"Synthèse pointages_Sheet" = Source{[Item="Synthèse pointages",Kind="Sheet"]}[Data],
    T_Deb= Date.From(Table.FirstValue(Excel.CurrentWorkbook(){[Name="T_Deb"]}[Content])),
    T_Fin= Date.From(Table.FirstValue(Excel.CurrentWorkbook(){[Name="T_Fin"]}[Content])),
    #"En-têtes promus" = Table.PromoteHeaders(#"Synthèse pointages_Sheet", [PromoteAllScalars=true]),
    #"Lignes filtrées" = Table.SelectRows(#"En-têtes promus", each [Date] >= T_Deb and [Date] <= T_Fin),
    #"Lignes triées" = Table.Sort(#"Lignes filtrées",{{"Date", Order.Ascending}})
in
    #"Lignes triées"

Essaie, et si tu n'y arrives pas, n'hésite pas

Bonne soirée

Le 06/01/2023 à 10:10

Super!!

Merci @cousinhub.

C'est absolument parfait!!!

Aller, juste une dernière question pour pimenter le sujet.

Est-il possible par Power Query d'indiquer que le chemin du fichier des données brutes "Fichier de pointage" est identique au chemin du fichier dans laquelle cette requête est effectuée?

Du style

Workbooks.Open Filename:=ThisWorkbook.Path & "\Fichier de pointages.xlsm"

Mais dans un langage Power Query que je connais encore moins que VBA...

Car ces 2 fichiers vont se retrouver sur un disque réseau, et bien évidemment personne ne possède les mêmes lettres de lecteur réseau...

Le 06/01/2023 à 11:17

Bonjour,

Ravi que tu aies réussi...

Pour automatiser également le chemin du fichier, tu peux, sur le même principe, utiliser une cellule quelconque...

Dans un nouvel onglet, par exemple, dans la cellule A1, tu mets cette formule :

=GAUCHE(CELLULE("nomfichier");TROUVE("[";CELLULE("nomfichier");1)-1)&"fichier-de-pointages.xlsm"

Cette formule te donnera le nom du fichier précédé du chemin complet.
Cette cellule, tu la nommes "Fichier" (maintenant, tu sais faire.... )

Et dans l'éditeur PQ, tu remplaces la première ligne "Source = ...) par celle-ci :

Source = Excel.Workbook(File.Contents(Excel.CurrentWorkbook(){[Name="Fichier"]}[Content][Column1]{0}), null, true),

(PS, n'oublie pas la virgule en toute fin, sinon tu as un msg d'erreur "Jeton Comma attendu")

Et c'est tout

Bon courage

Le 06/01/2023 à 11:52

Que dire à part un grand merci !!!

Pour ton temps passé sur mon sujet, et d'avoir aussi bien détaillé la procédure.

Cela m'est d'une grande aide pour continuer à progresser dans mes automatisations de fichiers.

Le 06/01/2023 à 13:49

Bonjour à tous !

Zed69 a écrit :

Je ne peux utiliser la requête Power Query car les personnes destinées à utiliser le fichier sont des utilisateurs très basique d'Excel.

Preuve s'il en est, un frein peut toujours être levé.....

Rechercher des sujets similaires à "recherche numeros lignes puis supprimer"