ucfoutu
Messages postés18038Date d'inscriptionlundi 7 décembre 2009StatutModérateurDernière intervention11 avril 2018
-
3 août 2012 à 10:42
MarcPL
Messages postés172Date d'inscriptionjeudi 8 décembre 2011StatutMembreDernière intervention21 juillet 2013
-
9 déc. 2012 à 12:04
Bonjour,
Parvenant cette nuit (pleine lune) difficilement à trouver mon sommeil et voulant me décontracter un peu à la suite de travaux manuels lourds, je me suis "attaqué" à un problème qui me turlupinait et qu'avait récemment mis en exergue un intervenant (un administrateur, si ma mémoire est bonne . Jde pense qu'il s'agissait de jack).
Caractéristiques du problème :
Cells.SpecialCells(xlCellTypeLastCell).Address
renvoie normalement l'adresse de la dernière cellule non vide d'une feuille de calcul Excel.
On observe cependant que si (exemple) on remplit une cellule au-delà de cette adresse, :
- est bien retournée la nouvelle adresse, mais que :
- si on efface cette même dernière cellule, on ne retourve pas la première adresse, comme il serait légitime de l'espérer.
Pour le démontrer :
MsgBox Cells.SpecialCells(xlCellTypeLastCell).Address ' adresse1
Cells.SpecialCells(xlCellTypeLastCell).Offset(3, 4).Value = "x" ' on remplit une cellule au-delà
MsgBox Cells.SpecialCells(xlCellTypeLastCell).Address ' on a bien la nouvelle adresse : adresse2
Cells.SpecialCells(xlCellTypeLastCell).Value = "" ' on efface la cellule additionnelle remplie et on devrait retrouver adresse1
MsgBox Cells.SpecialCells(xlCellTypeLastCell).Address' on a malheureusement encore adresse2 !
Voilàç qui est très gênant par on ne peut plus se fier à cette utilisation de la dernière cellule !
LA SOLUTION est simple :
Bien que l'aide de VBA ne souffle pas un mot de ce fait, la propriété UsedRange n'est pas en lecture seule... Utilisée seule, elle semble agir comme une instruction et "obliger" Excel à recalculer ses adresses !
Démonstration :
MsgBox Cells.SpecialCells(xlCellTypeLastCell).Address ' adresse1
Cells.SpecialCells(xlCellTypeLastCell).Offset(3, 4).Value = "x" ' on remplit une cellule au-delà
MsgBox Cells.SpecialCells(xlCellTypeLastCell).Address ' on a bien la nouvelle adresse : adresse2
Cells.SpecialCells(xlCellTypeLastCell).Value = "" ' on efface la cellule additionnelle remplie et on devrait retrouver adresse1
MsgBox Cells.SpecialCells(xlCellTypeLastCell).Address' on a malheureusement encore adresse2 !
' Allons-y
ActiveSheet.UsedRange ' <<<<<<<======================= elle est là, la baguette magique
MsgBox Cells.SpecialCells(xlCellTypeLastCell).Address ' et miracle ===>> on a alors bien adresse1
Ca, c'était pour partager avec vous cette "découverte" qui me laisse "baba" .
Mais j'ai testé avec ma version (Office 2007). Et je me méfie assez car je me demande si les autres versions réagiraient de même !
J'ai donc besoin des autres.
Ceux qui voudront bien tester avec d'autres versions et nous renseigner sur le résultat obtenu sont espérés et bienvenus.
Merci d'avance à tous ceux qui nous renseigneront.
________________________
Réponse exacte ? => "REPONSE ACCEPTEE" pour faciliter les recherches.
Pas d'aide en ligne installée ? => ne comptez pas sur moi pour simplement vous dire ce qu'elle contient. Je n'interviendrai qu'en cas de nécessité de développ
ucfoutu
Messages postés18038Date d'inscriptionlundi 7 décembre 2009StatutModérateurDernière intervention11 avril 2018211 4 août 2012 à 10:16
Ca y est !
J'ai cherché partout, puisque cet aspectr n'était pas traité dans l'aide VBA, d'une part et que, d'autre part, il me paraissait impossible que ce ne fût pas une fonctionnalité décidée par Microsoft !
Voilà sur quoi je viens de "tomber" :
Tapez le texte de l'url ici. Qui nous apprend :
- que c'est bien cela
- que c'est bon depuis 1997
Nous pouvons donc clore ce sujet.
Mais il est regrettable que cet aspect important n'ait pas été signalé dans l'aide VBA, qui va même (oh !) jusqu'à préciser que la propriété UsedRange est en lecture seule ! (un oubli des rédacteurs de l'aide ? Il semble bien que oui.)
Merci à tous
________________________
Réponse exacte ? => "REPONSE ACCEPTEE" pour faciliter les recherches.
Pas d'aide en ligne installée ? => ne comptez pas sur moi pour simplement vous dire ce qu'elle contient. Je n'interviendrai qu'en cas de nécessité de développ
ucfoutu
Messages postés18038Date d'inscriptionlundi 7 décembre 2009StatutModérateurDernière intervention11 avril 2018211 3 août 2012 à 14:33
Merci jack, pour cette information précieuse.
Ne nous manquent maintenant que les rapports pour 1997 et 2000.
Merci à ceux qui voudront bien compléter.
________________________
Réponse exacte ? => "REPONSE ACCEPTEE" pour faciliter les recherches.
Pas d'aide en ligne installée ? => ne comptez pas sur moi pour simplement vous dire ce qu'elle contient. Je n'interviendrai qu'en cas de nécessité de développ
Vous n’avez pas trouvé la réponse que vous recherchez ?
ucfoutu
Messages postés18038Date d'inscriptionlundi 7 décembre 2009StatutModérateurDernière intervention11 avril 2018211 4 août 2012 à 09:57
Bonjour, pile_poil,
Merci à toi également pour cette remontée.
Ne nous reste plus qu'à attendre 1997 et 2000 et on pourra clore ce sujet.
________________________
Réponse exacte ? => "REPONSE ACCEPTEE" pour faciliter les recherches.
Pas d'aide en ligne installée ? => ne comptez pas sur moi pour simplement vous dire ce qu'elle contient. Je n'interviendrai qu'en cas de nécessité de développ
ucfoutu
Messages postés18038Date d'inscriptionlundi 7 décembre 2009StatutModérateurDernière intervention11 avril 2018211 4 août 2012 à 21:34
Ouais ... Maintenant que l'on sait cela, on va essayer de l'utiliser adroitement.
J'ai déjà une utilisationb en tête : comment recenser rapidement (et en faire ce que l'on veut) les lignes entièrement vides d'une plage de cellules.
Je m'y mets à la fin de mes travaux de maçonnerie.
________________________
Réponse exacte ? => "REPONSE ACCEPTEE" pour faciliter les recherches.
Pas d'aide en ligne installée ? => ne comptez pas sur moi pour simplement vous dire ce qu'elle contient. Je n'interviendrai qu'en cas de nécessité de développ
djcoach
Messages postés7Date d'inscriptionmercredi 19 octobre 2011StatutMembreDernière intervention27 septembre 2012 26 sept. 2012 à 11:41
... désolé de venir faire le trouble fête...
Si une cellule est colorée en dehors de la zone de données, alors lastcell fait référence à cette cellule.
Si la couleur est supprimée en passant par "aucun remplissage" cette cellule est toujours la dernière.
Si la colonne a été supprimée ou si une cellule "vierge" a été déplacée ou recopiée sur la cellule en couleur alors on retourne à la vraie dernière cellule.
Un formatage de nombre dans une cellule vide ne fait pas considérer à Excel qu'il s'agit de la dernière cellule.
Idem pour une cellule vide référencée dans une formule.
Donc pour le moment, je vais continuer à chercher la dernière cellule avec des boucles sur les lignes et les colonnes.
Mais je me réjouissais d'une solution aussi rapide.
MarcPL
Messages postés172Date d'inscriptionjeudi 8 décembre 2011StatutMembreDernière intervention21 juillet 20132 1 oct. 2012 à 01:44
Je n'ai pas eu de souci avec .SpecialCells(xlCellTypeLastCell) car je l'utilisais directement avec .UsedRange ...
Par contre, comme djcoach, dès qu'une plage a été formatée après la dernière cellule saisie,
.SpecialCells(xlCellTypeLastCell) ne renvoie pas la dernière cellule non vide
mais la dernière cellule de cette plage formatée après la dernière cellule saisie !
Donc pour obtenir la dernière cellule d'une plage, autant utiliser la propriété .Count comme dans l'exemple suivant renvoyant la même adresse dans le cas d'une zone formatée après la dernière saisie :
Sub TestLastCell()
Set Rg = ActiveWorkbook.ActiveSheet.UsedRange
Debug.Print Rg.SpecialCells(xlCellTypeLastCell).Address, Rg(Rg.Count).Address
End Sub
Ensuite pour trouver la dernière cellule saisie,
combiner la propriété End à .Count peut s'avérer utile ...
___________________________________________________________________________________________________________________
Comme la vitesse de la lumière est supérieure à celle du son, certains ont l'air brillant avant d'avoir l'air con !
bigfish_le vrai
Messages postés1835Date d'inscriptionvendredi 13 mai 2005StatutMembreDernière intervention20 novembre 201315 1 oct. 2012 à 13:51
Bonjour,
je ne vais pas dire que je le savais, (c'est toujours facile après coup) mais ce problème est connu car ou trouve depuis longtemps, ici et la sur le net, quelque code dont l'objectif est de nettoyer une feuille excel de façon à ramener la zone utilisée (used range) à sont stricte minimum.
et pour ceux qui souhaite réduire la zone utilisé à la/aux dernier(s) cellule(s) non vide(s), j'utilise le code suivant:
Option Explicit
Enum LgnCol
Ligne = xlByRows
Colonne = xlByColumns
End Enum
Sub Demo()
UsedRangeRedim
End Sub
Sub UsedRangeRedim(Optional ByRef maplage As Range)
ActiveSheet.UsedRange
If maplage Is Nothing Then Set maplage = ActiveSheet.UsedRange
'recherche derniere colonne
DerniereLigneColumn maplage, Colonne
'recherche derniere ligne
DerniereLigneColumn maplage, Ligne
MsgBox maplage.Address
End Sub
Sub DerniereLigneColumn(ByRef maplage As Range, Direction As LgnCol)
Dim LastOne As Range, ResizeCol As Byte, ResizeRow As Byte
Do
Select Case Direction
Case Colonne
Set PlageCL = maplage.Columns(maplage.Columns.Count)
ResizeCol = 1
Case Ligne
Set PlageCL = maplage.Rows(maplage.Rows.Count)
ResizeRow = 1
End Select
Set LastOne = PlageCL.Find("*", , , , Direction, xlPrevious)
If LastOne Is Nothing Then
Set maplage = maplage.Resize(maplage.Rows.Count - ResizeRow, maplage.Columns.Count - ResizeCol)
Else
Exit Do
End If
DoEvents
Loop
End Sub
ucfoutu
Messages postés18038Date d'inscriptionlundi 7 décembre 2009StatutModérateurDernière intervention11 avril 2018211 3 oct. 2012 à 08:22
Tout-à-fait d'accord avec bigfish_le_vrai.
Il est important de nettoyer et de le faire par suppression systématique de ce qui n'est pas utilisé.
Pour revenir à l'objection soulevée : il est indispensable de savoir que, dan,s l'esprit de l'équipe Microsoft, une cellule dont l'intérieur a été modifié (la couleur, dans l'exemple) fait que Excel va l'inclure dans ce qu'il "analyse"
Il va le faire, par exemple, en utilisant la propriété xlCellTypeBlanks de specialcells
En rappelant que Excel limite toujours sa recherche de cellules "blanks" à la zone utile/utilisée.
Petite démo à faire sur un projet vierge) :
Je vais essayer de voir, dans la journée, comment s'y prendre astucieusement pour éviter tous ces pièges le moins lourdement possible.
________________________
Réponse exacte ? => "REPONSE ACCEPTEE" pour faciliter les recherches.
Pas d'aide en ligne installée ? => ne comptez pas sur moi pour simplement vous dire ce qu'elle contient. Je n'interviendrai qu'en cas de nécessité de développ
MarcPL
Messages postés172Date d'inscriptionjeudi 8 décembre 2011StatutMembreDernière intervention21 juillet 20132 3 oct. 2012 à 15:59
J'obtiens le même résultat directement en une ligne :
Sub Test()
Range("A4").Interior.Color = RGB(255, 0, 0)
MsgBox ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks).Address
End Sub
C'est vrai que cela marche bien sur un projet vierge
même en appliquant plus bas un format de nombre dans une cellule,
par contre sur un ancien projet c'est plutôt "incohérent" ...
___________________________________________________________________________________________________________________
Comme la vitesse de la lumière est supérieure à celle du son, certains ont l'air brillant avant d'avoir l'air con !
ucfoutu
Messages postés18038Date d'inscriptionlundi 7 décembre 2009StatutModérateurDernière intervention11 avril 2018211 3 oct. 2012 à 16:26
Bonjour, MarcPL,
Le problème est bien là :
Avec ou sans UsedRange (que je n'ai mis que pour bien lever toute ambiguïté) , la cellule ayant été coloriée "prend le pas", même vide.
________________________
Réponse exacte ? => "REPONSE ACCEPTEE" pour faciliter les recherches.
Pas d'aide en ligne installée ? => ne comptez pas sur moi pour simplement vous dire ce qu'elle contient. Je n'interviendrai qu'en cas de nécessité de développ
MarcPL
Messages postés172Date d'inscriptionjeudi 8 décembre 2011StatutMembreDernière intervention21 juillet 20132 9 déc. 2012 à 12:04
Sur un post d'un forum anglo-saxon datant de Novembre 2004 (!)
il est indiqué que parfois ActiveSheet.UsedRange ne réinitialise pas la plage des cellules utilisées
mais qu'avec Application.ActiveSheet.UsedRange il n'y a plus de problème ...
Je n'ai pas rencontré ce cas, il est vrai que j'utilise souvent le préfixe ActiveWorkbook.
___________________________________________________________________________________________________________________
Comme la vitesse de la lumière est supérieure à celle du son, certains ont l'air brillant avant d'avoir l'air con !