(Résolu) Comptage de ligne et bug vba

[romrom]

Bonjour !!!

Je viens vers vous avec une question simple : Qu'elle est à votre sens la manière la plus fiable de compter le nombre de ligne d'un fichier.

Personnellement, jusque là, j'utilisais sheets("feuille").usedrange.rows.count qui marche trés bien 99% du temps mais qui des fois bug... En effet, au lieux de me compter mes lignes remplies, la macro me prend en compte des lignes vides (voir l'ensemble du classeur) pour une raison qui m'échappe, a ce momment là je suis obliger de supprimer de la première ligne non remplie à la dernière. Même chose avec cells(X,Y).end(xldown).row (bien sûr dans le cas ou la colonne y ne permettent pas de laisser une case vide). Je me retrouve avec ce même problème de "cellules fantômes" prises en compte. Si cet outil m'était destiné je pourrai facilement vérifier et corriger à chaque fois, mais la personne à qui je souhaite le livrer ne pourra pas. En vous remerciant !!

Rom

 

[MJ13]

Re : Comptage de ligne et bug vba

Bonjour Romrom

J'aurais bien vu la traduction en VBA de la formule:

=NBVAL(A:A)

Mais le problème est que sur plusieurs colonnes, cela va être coton .

Sinon, il faut balayer la feuille sur chaque ligne de la première à la dernière avec Xltoright en espérant qu'il n' y en ait pas trop.

Mais il y a peut être une autre solution (cela mériterait même uh like).

 

[david84]

Re : Comptage de ligne et bug vba

Bonjour, salut Michel,
regarde cette discussion que j'avais initiée ici.
Les solutions proposées devraient te permettre de redéfinir correctement ta plage utile et donc de solutionner ton problème.
A+

 

[romrom]

Re : Comptage de ligne et bug vba

Merci bien les amis !!!

David, j'ai implémenté une des techniques que j'ai trouvé sur ton lien et ça marche du feu de dieux !!!! (les cellules avec des formalismes mais sans données ne sont pas prises en compte !!!! Génial). Maintenant, et si tu trouves du temps pour le faire et que ça ne te dérange pas, j'aimerais bien que tu m'expliques, pour ma gouverne, le sens de ces deux lignes : (excepté for, to et exit for ça ça va encore)

For r = .Row + .Rows.Count - 1 To .Row Step -1
If Application.CountA(sh.Rows(r)) <> 0 Then Exit For

Sinon il m'a semblé y voir une explication de ta part du même problème que je soulevais (des cellules vidées de leur contenu mais considérées comme "usedrange"). J'ai appelé ça "cellule fantôme" mais connais tu (toi David, ou toi qui lis ce post) un terme français ou anglais plus approprié afin que je puisse faire des recherches la dessus ?

Encore merci.

Rom

 

[david84]

Re : Comptage de ligne et bug vba

Bonsoir,

For r = .Row + .Rows.Count - 1 To .Row Step -1
If Application.CountA(sh.Rows(r)) <> 0 Then Exit For

 .Row + .Rows.Count - 1

cette ligne permet de caler le début du traitement par rapport à la plage de cellules utilisées de la feuille : si cette plage débute en ligne 1 (par exemple A1:H10) la valeur ramenée par UsedRange.Rows.count (10 en l'occurrence) correspondra à la dernière ligne (ligne 10), donc là pas de problème.

Par contre si cette plage débute en ligne 10 (par exemple A10:H20), la valeur ramenée par UsedRange.Rows.count sera toujours 10 mais si tu ne rajoutes pas à cette valeur les 9 lignes vides placées au-dessus de la plage de cellules utilisées, la 1ère ligne traitée par la boucle For...Next ne sera pas la ligne 20 mais la ligne 11.

UsedRange.Row + UsedRange.Rows.Count - 1

ajoute donc la 1ère ligne de la plage de cellules utilisées au nombre total de cette plage moins une ligne afin de pouvoir retomber sur ses pattes.

For r = UsedRange.Row + UsedRange.Rows.Count - 1 To UsedRange.Row Step -1

permet de boucler en partant de cette dernière ligne du UsedRange (qui ainsi calée devient égale à la dernière ligne utilisée de la feuille) et en remontant jusqu'à la 1ère ligne du UsedRange

To UsedRange.Row Step -1

(pas besoin de traiter au-delà puisque les lignes du dessus sont vides).
Step-1 est le pas négatif permettant de traiter la plage utile en remontant ligne par ligne.

Sinon il m'a semblé y voir une explication de ta part du même problème que je soulevais (des cellules vidées de leur contenu mais considérées comme "usedrange"). J'ai appelé ça "cellule fantôme" mais connais tu (toi David, ou toi qui lis ce post) un terme français ou anglais plus approprié afin que je puisse faire des recherches la dessus ?

Regarde le message #8 de la discussion indiquée dans mon précédent message et tu auras mon avis sur la question.
A+

 

[MJ13]

Re : Comptage de ligne et bug vba

Re, Bonjour David

Justement encore aujourdhui, j'ai mis un code ici:

https://www.excel-downloads.com/threads/astuce-du-jour-palette-de-couleur-sur-le-click-droit.202526/

Les lignes qui sont prises en compte sous les données et qui sont vides sont souvent du à des extractions de données ou des copies d'une grande plage, voire des TCDs raccourcis (j'ai encore eu le cas aujourd'hui).

 

[romrom]

Re : Comptage de ligne et bug vba

Merci les amis, grâce à vous j'ai réglé mon problème et appris des choses !!!

David je te remercie en particulier pour avoir pris le temps de m'expliquer point par point ce que je t'avais demandé, et de l'avoir fait aussi vite, c'est maintenant clair comme de l'eau de roche. C'est vraiment génial de pouvoir compter sur des gens comme ça, j'apprécie vraiment !!! J'espère être un jour suffisamment calé pour pouvoir aidé, aussi bien que je l'ai été, un petit noob comme moi.

Encore merci à tous les participants et à bientôt pour de nouvelles aventures au pays des macros

Rom

 

[david84]

Re : (Résolu) Comptage de ligne et bug vba

Bonjour Michel,
je viens de tester ton code sur le fichier test déposé dans la discussion indiquée au message #3 : le résultat ramené (ligne5) ne prend pas en compte les lignes renseignées mais masquées (il y a des données dans les lignes 6, 7, 8 et 18).
Je n'ai pas regardé le code dans le détail mais je pense que c'est l'utilisation de SpecialCells(xlLastCell) qui fausse le résultat.
Je pense que l'utilisation de UsedRange pour délimiter la plage utilisée est plus adaptée car les ligne masquées sont prises en compte.
L'une des conséquence est que la ligne 18 (ligne masquée) qui comporte pourtant une valeur est supprimée.
A+

 

[MJ13]

Re : (Résolu) Comptage de ligne et bug vba

Re, Bonjour David

Je pense que l'utilisation de UsedRange pour délimiter la plage utilisée est plus adaptée car les ligne masquées sont prises en compte.
L'une des conséquence est que la ligne 18 (ligne masquée) qui comporte pourtant une valeur est supprimée.

Ah, merci David de m'avoir signaler ce problème. En effet, javais testé avec des lignes masquées, mais pas vers les dernières lignes. Dans ce cas, il vaut mieux utiliser, comme tu l'as démontré, UsedRange ou Find qui pour moi est plus compréhensible.

Pour éviter ce problème dans mon cas, je ferais donc un affichage de tout afin d'éviter de perdre des données lors d'une prochaine ouverture du fichier. Le code se présentrerait ainsi:

Sub Combien_Lignes_Colonnes_Feuille_Supprime_Lignes_Vides_Basses()
'MJ
t1 = Timer
'MsgBox ActiveSheet.FilterMode
'MsgBox ActiveSheet.AutoFilterMode
If ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode = True Then ActiveSheet.ShowAllData
Cells.Select
Cells.EntireRow.AutoFit
Dim DerLigRempli(16384) 'As Long
derl = [A1].SpecialCells(xlLastCell).Row
DerC = [A1].SpecialCells(xlLastCell).Column
For i = 1 To derl
If Application.WorksheetFunction.CountA(Range(Cells(i, 1), Cells(i, Columns.Count))) > 0 Then NbligRempli = NbligRempli + 1
Next
For j = 1 To DerC
If Application.WorksheetFunction.CountA(Range(Cells(1, j), Cells(Rows.Count, j))) > 0 Then NbColRempli = NbColRempli + 1
Next
'Suppresion des lignes vides après la dernière ligne
DerligRenseignee = Cells(Rows.Count, 1).End(xlUp).Row
For k = 1 To DerC
DerLigRempli(k) = Cells(Rows.Count, k).End(xlUp).Row
If DerLigRempli(k) > DerligRenseignee Then DerligRenseignee = DerLigRempli(k)
Next
'A décommenter en cas de besoin
'MsgBox DerligRenseignee
'Supprime les lignes vides sous les denières données
Rows(DerligRenseignee + 1 & ":" & Rows.Count).Delete
'Sauvegarde le fichier actif
'ActiveWorkbook.Save
t2 = Timer - t1
MsgBox DerC & " colonnes" & " dont " & NbColRempli & " Colonnes remplies." & Chr(10) & derl & " lignes " & " dont " & NbligRempli & " lignes remplies." & Chr(10) & "Durée de traitement: " & Format(t2, "0.0") & "secondes" & Chr(10) & "La dernière ligne renseignée est la ligne: " & DerligRenseignee
End Sub