Supprimer des lignes vides et sous-total
Résolu
nancy_soleil08
Messages postés
63
Date d'inscription
Statut
Membre
Dernière intervention
-
nancy_soleil08 Messages postés 63 Date d'inscription Statut Membre Dernière intervention -
nancy_soleil08 Messages postés 63 Date d'inscription Statut Membre Dernière intervention -
Bonjour,
J'ai un gros fichier dont le nombre de ligne varie chaque jour. J'ai plusieurs formules dans le fichier et comme je ne sais jamais combien de ligne j'exporterai dans mon fichier, j'ai mis mes formules jusqu'à la ligne 350 pour être certaine de tout inclure (C'est des formules si et quand la réponse est fausse, le résultat est "" pour que cela n'inscrive rien). J'ai fait une macro subtotal, mais elle inclus les lignes vides que je n'ai pas utilisées mais qui contiennent des formules avec réponse "". Je voudrais supprimer les lignes dont la valeur de la cellule de la colonne A est vide pour après faire mon sous-total en incluant seulement les lignes avec des données.
Merci
J'ai un gros fichier dont le nombre de ligne varie chaque jour. J'ai plusieurs formules dans le fichier et comme je ne sais jamais combien de ligne j'exporterai dans mon fichier, j'ai mis mes formules jusqu'à la ligne 350 pour être certaine de tout inclure (C'est des formules si et quand la réponse est fausse, le résultat est "" pour que cela n'inscrive rien). J'ai fait une macro subtotal, mais elle inclus les lignes vides que je n'ai pas utilisées mais qui contiennent des formules avec réponse "". Je voudrais supprimer les lignes dont la valeur de la cellule de la colonne A est vide pour après faire mon sous-total en incluant seulement les lignes avec des données.
Merci
17 réponses
Résumé de la discussion
Un fichier Excel comporte des formules jusqu'à une ligne fixe (350), mais le nombre de lignes réelles varie chaque jour, et des lignes vides perturbent le calcul du sous-total.
Plusieurs propositions visent à supprimer en amont les lignes dont la colonne A est vide, afin d’inclure uniquement les données et d’éviter que le sous-total s’étale sur des lignes inutiles.
Des ajustements portent aussi sur la gestion des lignes de total et grand total, et sur la possibilité de tester une macro qui supprime les lignes vides avant le calcul.
Des échanges évoquent aussi des détails pratiques comme le décalage éventuel et des formules du type =IF(...), avec des retours sur les performances et les mises à jour.
-
Re, re bonjour
un exemple dû à michel
http://www.cjoint.com/c/FDAp67Z8OWQ
Cdlmnt -
Je voudrais supprimer les lignes dont la valeur de la cellule de la colonne A est vide pour après faire mon sous-total en incluant seulement les lignes avec des données.
à mettre dans un moduleOption Explicit
Const coID = "A"
Public Sub SuppLignesVides()
Dim lifin As Long, li As Long
With ActiveSheet
Application.ScreenUpdating = False
lifin = .Range(coID & Rows.Count).End(xlUp).Row
li = lifin - 1
While Range(coID & li).Value = ""
.Rows(li).Delete
li = li - 1
Wend
End With
End Sub
RQ. Tu as une foule de modules vba vides, seuls le modules 9 et 19 contiennent du code il te faudrait faire le ménage
CDlmnt-
J'ai jamais eu de formation en macro, je sais enregistrer ce que je fais manuellement pour ensuite visualiser ce que cela donne en macro et je sais me débrouiller pour y modifier les choses que je veux par la suite dans la macro et jouer avec, mais je ne sais pas exactement comment créer une macro en entier en entrant chaque information comme vous faites. Je connais la base, la comprends, mais il m'est difficile de l'appliquer sans me mélanger! Alors modules vba vide c'est probablement tous les enregistrements que j'ai fait et que j'ai parfois supprimés, parfois jumelés avec d'autres macros. Je vais regarder cela. Merci
Et pour ce qui est d'insérer d'autres équations dans ma ligne de sous-total dans différentes colonnes, est-ce possible?
-
-
Et pour ce qui est d'insérer d'autres équations dans ma ligne de sous-total dans différentes colonnes, est-ce possible?
Je ne comprends pas bien ta demande
Une question quand même. Est ce que tu veux griser toutes les lignes qui contiennent le mot Total (sauf peut être quelques unes à préciser et Grand Total) ?
Cdlmnt-
-
Ma demande est qu'une fois la fonction sous-total effectuée, j'obtiens les lignes des sous-totaux que j'ai demandés pour la colonne G, H, Q et R. Mais j'ai aussi d'autres équations(pas des sous-totaux) que je rajoute manuellement sur mes lignes sous-total dans les colonnes J, K, O, S, Z. Donc au final, la ligne de sous-total comprends des données dans les colonnes G, H, Q, R & J, K, O, S, Z. J'aimerais ne plus les faire manuellement et trouver une macro qui rajouterait ces équations automatiquement dans la ligne qui aura été générée par la fonction sous-total. Pas facile à expliquer!
-
-
1. Oui dans l'ensemble! OHHHHHH je pense savoir, je pourrais utiliser "Conditional formatting" (Je sais pas le nom en français!)
Mise en forma conditionnelle (MFC)
Ce pourrait être une solution, mais il te faut l'appliquer à toute la feuille, ça va alourdir ton fichier (déjà bien lourd), et comme de toute façon tu as des macros dans ton fichier, autant le faire faire à une macro
2. J'aimerais ne plus les faire manuellement et trouver une macro qui rajouterait ces équations automatiquement dans la ligne qui aura été générée par la fonction sous-total.
Si j'ai compris, à la fin d'une série de Report ID identiques
Tu insères une ligne sous total et la macro
- la colorie en gris
- écrit les formules dans les colonnes G, H, Q, R & J, K, O, S, Z
En fait, la macro peut se charger de tout, insertion comprise, puisque les sous totaux se font sur les séries de Report ID indentiques
C'est ça ? -
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre question -
Un début à tester
http://www.cjoint.com/c/FDCmULCPLuQ
Cdlmnt -
1. Lorsque tu es en erreru dans l'editeur vba sur la ligne jaune, passes le curseur sur li, li1 et li2 pour voir quelle est leur valeur
2. tu as une version d'excel en français ou en anglais ?
Essaies en remplaçant SOUS.TOTAL par SUBTOTAL
Cdlmnt -
-
Les lignes et la formule sont corrects (du moins chez moi sur mon vieil excel 2003)
Peux tu utiliser l'enregistreur de macros pour créer une nouvelle macro et ecrire la formule dans la cellule G343 à la main.
Tu m'envoies ce que vba a écrit.-
Sub test()
'
' test Macro
'
'
Range("A1:Z342").Select
Range("C339").Activate
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(7, 8, 17, _
18), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End Sub
Ça c'est ce que la macro enregistre quand je fais un sous-total, mais dans g343, il n'y a aucune valeur quand la macro bug si j'essais la macro M
J'ai remarqué que la macro qui ne fonctionne pas génère une ligne à la fin avec aucun sous-total dedans pour le dernier reportID de la colonne A Je te renvois le fichier avec la ligne en rouge.
http://www.cjoint.com/c/FDCs1x7QZb8
-
-
Non, ne selectionnes pas la plage avant de lui appliquer la fonction, tu tapes toute la formule à la main ... = SUBTOTAL(G.... etc ...
Je regarde ton fichier en attendant-
-
-
1. Ton ficher a souffert (vois la colonne A), je te le renvoies
2. Non, je crois que tu n'as pas tapé la formule à la main !
en G343 tu tapes la formule =SUBTOTAL(9,G334:G342)
ou SOUS.TOTAL(9;G334:342) je ne sais pas
ça devrait ressembler à ça
Range("G343").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-9]C:R[-1]C)"
et tu renvoies le fichier
http://www.cjoint.com/c/FDCtDxZqiLQ
Cdlmnt -
-
-
-
Bon essaies celui ci et sans décaler ;-)
http://www.cjoint.com/c/FDCubCJjqfQ-
-
Je viens de transférer votre macro dans mon fichier réel afin de tester. J'ai activé ma macro en premier et ensuite la votre. Tout est parfait sauf pour le dernier sous-total. Le total de la ligne 383 devrait être après la ligne 384 et inclure la ligne 382 et 384 (Qui serait ligne 383 si le total était au bon endroit.) Parce que la valeur de la colonne A pour ligne 382 & 384 est la même donc aurait dû être calculé ensemble. Je ne sais pas pourquoi cela ne fonctionne pas juste pour ces lignes, parce que le reste est parfait. Et, j'ai vraiment l'impression d'abuser, mais la ligne Grand-total qui englobait le total de toutes les lignes Total n'existe plus. Quand on utilise la fonction sous-total, habituellement cela créé aussi un grand total. Il est facile pour moi de l'ajouter manuellement, mais si c'est pas trop compliquer de la mettre dans la macro.
Je vous mets mon fichier à jour pour que vous puissiez comprendre ce dont je parle.
http://www.cjoint.com/c/FDDmJEYW7Z8 -
-
-
1. sauf pour le dernier sous-total. Le total de la ligne 383 devrait être ..... mais la ligne Grand-total qui englobait le total de toutes les lignes Total n'existe plus
Normal, la position du premier sous total (en bas de tableau) est prévue par la macro sur la ligne précédent la ligne Grand Total, si celle ci n'existe plus, estimes toi heureuse que le programme n'ait pas explosé ton ordinateur ;-)
Le programme ne peut réaliser que ce qu'on lui a dit de faire et ... pas formcément ce qu'on aimerait qu'il fasse !
2. Donc tu dois me confirmer soit que la ligne Grand total n'exsitera plus, soit qu'elle peut éventuellement exister.
2.1 l'ajout de la ligne Grand total à la fin ne devrait pas poser de problème.
2.2. la macro ne marchera pas si la ligne grand total existe (ou alors, il faudra que la procedure SuppLignesVides efface aussi la ligne grand total)
Cdlmnt-
-
La ligne grand total n'existe jamais à la base, c'est la fonction excel de sous-total qui me générait cette ligne, mais dans ce que j'importe dans excel, il n'y a jamais de ligne grand total, la dernière ligne serait ce qui est inscrit dans la ligne 384.
Pour ce qui est de Sheets("Production jeu")
je croyais devoir rajouter cela car, à la base, dans le fichier que vous m'avez créé, il n'y avait que la journée de mardi dans le fichier, mais moi, en réalité, j'ai un onglet de lundi à vendredi et je dois appliquer cette macro indépendamment chaque jour alors je ne savais pas comment dire à votre macro de l'appliquer à "production jeu". J'avais vu le activesheet, mais je ne comprenais pas comment le activesheet faisait pour s'appliquer à la journée que je voulais. Mais en collant un "Bouton" dans chacune de mes feuilles, je comprends qu'il traitera la feuille ou il y a le bouton.
Alors FIOU mon ordinateur n'a pas explosé! C'est au travail et il est neuf!!! ;-) Mais c'est ce qui fait la différence entre vous et moi, vous êtes l'expert, je suis la débutante!
-
-
Voilà
http://www.cjoint.com/c/FDDoaD4MvAQ
Le passage par le convertisseur (j'ai excel 2003) l'a fait sérieusement grossir
Il vaut mieux que tu copies les macros dans ton fichier. Profites en pour supprimer tous les modules vba vides
A tester dans la feuille jeudi bien sûr
Si tu veus tester dans les autres, il faut au préalable lancer la procedure supprime les lignes total (Ctrl+s)
Cdlmnt -
???
Chez moi, j'obtiens ceci, c'est ce que tu veux ?
http://www.cjoint.com/c/FDDqveRkBVQ
Pour tester sur jeudi. Tu te mets dans la feuille jeudi
Ctrl+t pour supprimer les lignes total
Ctrl+m pour lancer la bête
Il restait un Sheets("Production Jeu").Select dans la procedure Formules
Cdlmnt-
C'est vraiment parfait, j'avais oublié la petite formule de la colonne Z dans la ligne total. Mais avec toutes vos indications très claires, je vais m'essayer de la rajouter dans votre macro et si jamais je n'y arrive pas, je vous le demanderai. La macro qui supprime les cellules vide ne supprime pas les formules dans les lignes 315 à 350. Ma macro étant beaucoup plus de base que la votre et ne sachant comment bien créer une boucle, j'avais mis des formules jusque dans la ligne 350 au cas ou j'en importerais plus qu'à l'habitude. Mais au final cela ne change rien au résultat, seulement à l'impression alors je vais juste les supprimer avant d'imprimer.
Je vous l'ai dit plus d'une fois, mais VRAIMENT, un gros gros merci pour votre aide. En si peu de temps, vous m'avez permise de rendre mon fichier bien plus rapide et efficace. Chose que je n'aurais pas su réaliser seule. Si vous n'étiez pas si loin, je vous demanderais de me donner une formation! Je ne sais pas ce que vous faites comme métier, mais merci encore vraiment ce temps que vous m'avez consacré.
Cordialement,
Nancy
-
-
1. La macro qui supprime les cellules vides ne supprime pas les formules dans les lignes 315 à 350.
Remplaces la procedure SuppLignesVides par celle ci (pour le même prix elle est plus rapide)Public Sub SuppLignesVides()
Dim lifin As Long, li As Long, lader As Long
With ActiveSheet
Application.ScreenUpdating = False
lader = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
lifin = .Range(coID & Rows.Count).End(xlUp).Row
.Rows(lifin + 1 & ":" & lader).Delete
End With
End Sub
2. La fomule en colonne Z ... tu as un joli chantier !
Cdlmnt -
Pour la formule en colonne Z, quand je dis que c'est un chantier
f = "=IF(C" & li & "=""rm"",""OK"","""")"
Cells(li, 1).Formula = f
' avec li=3, ça donne sur la feuille en ligne li et colonne 1
' =SI(C3="rm","OK","")
- quand tu veux afficher un guillemet tu en mets deux
- tu "découpes" avec des & pour insérer le n° de ligne
Bref, de quoi occuper ton week end ;-)
Au fait une question. Sur ta machine (moderne) la mise à jour prend combien de temps ?
Cdlmnt-
12 secondes, mais j'attends mon technicien la semaine prochaine, car je trouve mon nouveau Excel 2016 lent comparé à ma version 2007. Peut-être parce qu'il n'a pas encore désinstallé le 2007 et que je me retrouve avec les 2 en même temps. Mais bref la semaine prochaine sera encore mieux! Et toi combien de temps sur ton ordi?
-
-
-
1. Chez moi 13-15 s selon la dispo du processeur, pas trop mal pour une machine et un excel d'un âge vénérable
2. Tu as bien recopié les macros dans un classeur qui n'est pas passé chez moi, Le convertisseur le fait passer de 2 Mo à 8 Mo, donc ça ralentit forcément l'exécution
3. Pour la formule en colonne Z, c'est bien une recopie de celle qui est en dessus ? Si c'est oui, pas la pêne de t'arracher les cheveux, tu insères les deux lignes soulignées dans la procédure Formules.Range("S" & li).Formula = f
' formule colonne Z recopiée depuis la cellule en dessus
.Range("Z" & li - 1).AutoFill Destination:=Range("Z" & li - 1 & ":Z" & li), Type:=xlFillDefault
' mise en gras et en gris
Bon week end-
Bonjour! J'ai fait mon premier rapport ce matin, tout est vraiment parfait. C'est gentil de m'avoir inscrit toute la formule pour ma colonne z, malheureusement, la formule qui était à inscrire n'était pas celle au dessus mais celle-ci:
=IF($V6>0,$V6,$Z$1) (le "6" change selon la ligne dans laquelle on colle cette formule bien entendu).
Pour tout le reste c'est vraiment GÉNIAL! Et mon Excel est réparé, Temps=3.02 Secondes. Mais je pense quand même que votre ordinateur est pas si mal pour son âge!
Ma connexion à distance ne fonctionnait pas ce week-end, je n'ai donc pas pu y travailler de chez moi!
Merci encore beaucoup! -
-
Laisse tomber, parce que s'il n'y a pas de résultat dans la colonne V, j'aurai comme résultat du sous-total min: 0 Jan 00 et je voulais que le résultat me donne une date seulement s'il y en avait une au préalable dans la colonne V pour l'ensemble de mon Report ID qui était calculé dans la ligne Total Gris. Pas clair, mais surtout trop compliqué. Je réfléchirai la prochaine fois avant d'écrire!!!
-
-
=IF($V6>0,$V6,$Z$1) (le "6" change selon la ligne dans laquelle on colle cette formule bien entendu).
Je suppose que tu as pu la programmer, elle n'est pas trop compliquée
3 s pas mal, mais ça fait un peu juste pour boire le café !
Là, je vais être pas mal occupé pendant quelques jours (ah les petits enfants) et je ne pense pas revenir (bosser) sur CCM
.
Si tu as besoin de me recontacter sur ce programme, tu peux utiliser la messagerie personnelle (en haut à droite la petite enveloppe à côté de ton pseudo)
Si c'est fini, peux tu mettre le sujet à résolu (en dessous du titre de ton premier message)
Bonne suite dans ton travail
Cordialement
ccm81-
-
J'ai essayé la formule SI comme suit:
f = "=if(v" & li & ">""0"",""v" & li & ",""y" & li & ")"
.Range("z" & li).Formula = f
Mais j'ai fait quelque chose de pas correct car cela ne fonctionne pas.
J'ai essayé aussi avec Cells(li,1).formula = f au lieu de .range...
Mais cela ne fonctionne pas plus et je comprends pas assez bien pour trouver mon erreur... Je vais avoir encore besoin de votre aide, désolé!!!
-
