XL 2010 Utilisation de UsedRange

[cathodique]

Bonjour,

Il y a de cela un bon moment que @patricktoulon m'avait montré sa façon d'utiliser UsedRange pour récupérer la dernière ligne non vide en utilisant UsedRange.

Sub Der_Lig_Col_Usedrange()
   Dim DerLig As Integer, DerCol As Integer
   With ActiveSheet
      DerLig = .UsedRange.Cells(.UsedRange.Cells.Count).Row
      DerCol = .UsedRange.Cells(.UsedRange.Cells.Count).Column
   End With
   MsgBox "DerLig=" & DerLig & vbLf & "DerCol=" & DerCol
End Sub

Ce n'est pas très gênant pour moi mais j'ai envie de comprendre pourquoi le code me renvoie le numéro de ligne +1.
J'ai sûrement raté un truc. Je arrive pas à m'expliquer le pourquoi de ce +1 .
Merci.

 

[jurassic pork]

Hello,
A mon avis tu as du contenu sur la ligne 27 :
j'ai fait un UsedRange.Select et on voit la ligne 27 sélectionnée.
Si on supprime la ligne 27 , c'est bien la ligne 26 qui est désignée comme dernière ligne.
Ami calmant, J.P

 

[cathodique]

Je vous remercie.
Il m'arrive de plus en plus à regretter d'avoir ouvert une discussion.

'dl = .UsedRange.Rows.Count 'Attention c'est pas bon si le usedrange ne commence pas en ligne 1 dl = .UsedRange.Cells(.UsedRange.Cells.Count).Row 'Là on est sur dans tout les cas !!!!

Un jour, on est sûr et un autre jour, on invoque des astres qui laissent trainer ici et là des espaces.

OK moi aussi je sors

 

[mapomme]

Bonsoir à tous,

Tout comme atteindre la dernière cellule (via la touche de fonction F5), UsedRange n'est pas plus fiable.
"Dernière cellule" et Usedrange donnent des majorants de la dernière ligne et de la dernière colonne.
Il faut le savoir et coder en conséquence, c'est tout.

Pour s'amuser le classeur joint. Les cellules en dehors de la plage A1 : B7 sont et resterons vides.
Saisir un numéro de ligne n et de colonne j pour qu'Excel fixe la cellule cells(n,j) comme étant la dernière cellule (et pourtant cette dernière cellule ne comporte ni valeur ni formule).

nota : c'est un peu du "foutage de goule" mais ça le fait.

 

[mapomme]

Re,

Si le nombre de cellules de UsedRange est très grand, alors il faut remplacer Count par CountLarge pour éviter un débordement de capacité.

Un extrait de ce qu'a écrit Krosoft :

La propriété CountLarge est fonctionnellement la même que la propriété Count , sauf que la propriété Count génère une erreur de dépassement si la plage spécifiée comporte plus de 2 147 483 647 cellules (une moins de 2 048 colonnes). La propriété CountLarge, toutefois, peut traiter des plages ayant la taille maximale d'une feuille de calcul, c'est-à-dire 17 179 869 184 cellules.

ou alors utiliser le code suivant pour ne pas être confronter au 'débordement de capacité' quelque soit la taille du UsedRange :

   With ActiveSheet
      DerLig = .UsedRange.Row + .UsedRange.Rows.Count - 1
      DerCol = .UsedRange.Column + .UsedRange.Columns.Count - 1
   End With

nota : de plus, un numéro de ligne se déclare en Long pas en integer. Un integer est limité à 32 767 bien moins que le nombre de lignes d'une feuille de calcul.

 

[cathodique]

Messieurs je vous remercie,

Je me suis rappelé que @pierrejean (que je salue, il y a un moment que je ne l'ai pas rencontré. j'espère qu'il va bien) avait posté un code pour récupérer le numéro de la ligne réelle.
Je me suis inspiré de son code (encore merci @pierrejean )

Option Explicit

Sub TestDerReelle()
    Dim lastRow As Long, lastCol As Long

    der_reelle ActiveSheet.UsedRange, lastRow, lastCol
    MsgBox "Dernière ligne non vide : " & lastRow & vbCrLf & "Dernière colonne non vide : " & lastCol
End Sub

Public Sub der_reelle(plage As Range, ByRef lastRow As Long, ByRef lastCol As Long)
    lastRow = 1: lastCol = 1
        ' Si plage vide on sort de la fonction
    If WorksheetFunction.CountA(plage) = 0 Then
        Exit Sub
    End If

    lastRow = plage.Find("*", , , , , xlPrevious).Row
    lastCol = plage.Find("*", , , , , xlPrevious).Column
End Sub

Bonne soirée.

 

[cathodique]

nota : de plus, un numéro de ligne se déclare en Long pas en integer. Un integer est limité à 32 767 bien moins que le nombre de lignes d'une feuille de calcul.

Sachant que mes 3 petits tableaux ne dépasseront jamais la dizaine de lignes. Même en déclarant à byte, je n'aurai pas de dépassement.
En tout cas merci d'avoir attiré mon attention.

Bonne soirée

 

[cathodique]

ha ben là on est sur mars
on voyage c'est bien

le problème avec ton truc cathodique c'est qu'il faut une plage définie au départ
exemple ta feuille est remplie avec des cellules vides ici et là de A1 à j20
set plage=[A1:J20]
si ta cellule j20 ou non remplie dans cette colonne n'est pas remplie ben ton résultat sera faux
autrement dit il te faut dimensionner une plage bien plus gran,de au départ

on t'a donné deux solutions qui te permettent de te passer de tout renseignement au départ
si on transforme tout ça en fonction avec une structure (type)

Type prop
    Row As Long
    Column As Long
End Type

Function LastOccuped() As prop
    Dim p As prop
    With ActiveSheet.UsedRange
        p.Row = .Cells(.Cells.CountLarge).Row
        p.Column = .Cells(.Cells.CountLarge).Column
    End With

    LastOccuped = p
End Function

Sub test()
MsgBox "ligne : " & LastOccuped.Row & vbCrLf & "colonne : " & LastOccuped.Column
End Sub

Le problème est que tu penses à la place du demandeur qu'au départ la plage doit être définie.
Or, c'est faux. Je ne sais pas à l'avance combien mes petits tableaux auront de lignes.

Tu m'as proposé plusieurs codes dont le 1er pour récupérer la dernière ligne non vide d'un usedrange.
Tu étais sûr de toi. j'avoue que tu m'avais convaincu. Mais je me suis aperçu aujourd’hui qu'il renvoie une valeur erronée.
J'utilise ton code sur d'autres fichiers. je dois revoir un tas de fichiers.
Là, tu me proposes aussi un code et me dire qu'il est béton.
Je préfère tester le mien qui semble donner un résultat juste.

Merci quand même, bonne soirée.

 

[cathodique]

@patricktoulon : Tu fais une rechute comportementale de Developpez.com
Oui, toi tu sais tout et les autres pensent savoir ou ne savent pas du tout.
Dis-moi, est-ce tu t'es marré aussi parce que je t'ai cru pour ça:
.UsedRange.Cells(.UsedRange.Cells.Count).Row 'Là on est sur dans tout les cas !!!!
Ce truc donne un résultat erroné. Comme tu sais, tu savais donc et tu m'as filé un truc tordu.

Au fait, tu as dit que tu sortais. Pourquoi es-tu revenu? pour me filer encore un truc tordu pour te fendre la poire.

Bonsoir.

 

[TooFatBoy]

Pour s'amuser le classeur joint. Les cellules en dehors de la plage A1 : B7 sont et resterons vides.
Saisir un numéro de ligne n et de colonne j pour qu'Excel fixe la cellule cells(n,j) comme étant la dernière cellule (et pourtant cette dernière cellule ne comporte ni valeur ni formule).

Mais dis-moi Michel, comment ça marche ?

[edit]
C'est bon, je sais comment ça marche. Merci Michel...
[/edit]

 

[crocrocro]

Bonjour le fil,
il semble que cette discussion soit très animée et que les esprits s'échauffent un peu ....
certaines remarques sont, même si ce n'est pas intentionnel, blessantes.
Il me semble que l'on est ici dans un forum d"entraide, non ?
Pour revenir sur le sujet, je me suis parfois demandé, faut-il utiliser cette méthode plutôt que celle-là (UsedRange ou Find) ?
Et pour moi, la 1ère qualité d'une méthode, c'est sa fiabilité. une erreur sur 10 000 en météo c'est très fiable, pour un ordinateur non : 1 000 000 d'opérations à la seconde signifierait 100 erreurs par seconde !!! Viré !
En pj un fichier avec un exemple et donc une anomalie que je ne comprends pas.
Un pouce levé à qui m'en donnera la raison. Pas la raison pour laquelle je ne comprends pas, par exemple "t'es nul" mais avec pédagogie me fasse dire "mais c'est bien sûr !"

 

[mapomme]

Re ,

Il faut savoir qu'une cellule peut très bien être utilisée sans rien contenir.
Dernière cellule utilisée n'est pas dernière cellule contenant une constante ou une formule.

Si dans une feuille, vous appliquez un format à une cellule alors cette cellule fera partie des cellules utilisées bien que ne contenant rien. Pour essai, laissez la cellule XFD1048576 vide et appliquez lui le format "Texte".
Maintenant la dernière cellule de UsedRange est cette cellule XFD1048576.

Ne pas confondre Utilisée et contenant quelque chose . C'est peut-être aussi à cause de cela qu'on peut ne pas se comprendre.

Ceci dit concernant les erreurs évoquées par @crocrocro (que je salue ), on sait bien qu'en électronique un bit peut s'inverser sans aucune intervention délibérée. De là des méthodes ont découlé (redondance, parité, somme de contrôle, bit de contrôle, etc) pour s'assurer que les données restent le plus possible "justes".

L'important est de savoir comment réagir en connaissant la possibilité d'erreur. Je veux dans une colonne la dernière cellule comportant une donnée (constante ou formule) et je sais que les méthodes fournissent seulement un numéro majorant de la dernière ligne. Alors qu'à cela ne tienne, à partir de ce numéro de ligne, je remonte vers le haut et teste chaque cellule jusqu'à obtenir une cellule non vide et j'ai ma dernière ligne. Il y a eu erreur (ou pas) au départ, j'en suis conscient et j'agis en conséquence pour trouver le bon résultat.

 

[cathodique]

Bonjour le fil,
il semble que cette discussion soit très animée et que les esprits s'échauffent un peu ....
certaines remarques sont, même si ce n'est pas intentionnel, blessantes.
Il me semble que l'on est ici dans un forum d"entraide, non ?
Pour revenir sur le sujet, je me suis parfois demandé, faut-il utiliser cette méthode plutôt que celle-là (UsedRange ou Find) ?
Et pour moi, la 1ère qualité d'une méthode, c'est sa fiabilité. une erreur sur 10 000 en météo c'est très fiable, pour un ordinateur non : 1 000 000 d'opérations à la seconde signifierait 100 erreurs par seconde !!! Viré !
En pj un fichier avec un exemple et donc une anomalie que je ne comprends pas.
Un pouce levé à qui m'en donnera la raison. Pas la raison pour laquelle je ne comprends pas, par exemple "t'es nul" mais avec pédagogie me fasse dire "mais c'est bien sûr !"

Bonsoir @crocrocro ,

Je partage sur toute la ligne ton avis.
Surtout l'entraide. Ce qui m'a désappointé et la moquerie de @patricktoulon.
Il m'a aidé plusieurs fois, je l'ai remercié (et j'en suis reconnaissant).
Cependant, il reconnait rarement qu'il s'est trompé.
Il m'avait dit que sa ligne de code (plus haut) donnait un bon résultat.
Je l'utilise sur plusieurs de mes fichiers. Mais aujourd'hui, je ne suis rendu compte qu'elle renvoyait un résultat erroné.
Je n'ai pas supporté ses moqueries. On peut plaisanter, faire de l'humour mais pas rabaisser le demandeur.
C'est ainsi que je l'ai perçu suite à sa réflexion: "moi je sais, je ne pense pas".
à aucun moment, il n'a reconnu que sa précédente ligne de code n'était pas fiable.

Je n'ai encore ouvert ton fichier. Je reviendrais aux nouvelles.

Merci à tout le monde.

 

[cathodique]

Bonsoir,
Il semblerait que The KingOf ait enlevé toutes ses interventions...
Dommage, il me faisait bien rire...
Bonne soirée

Pas moi, surtout aujourd'hui.
C'est ça l'esprit d'entraide. Je suis très déçu.

Bonne soirée.

 

[klin89]

Bonsoir le forum

Le souci avec UsedRange, c'est qu'il tient compte du format entre autre*.
Par exemple, formatez une cellule vide avec une police de couleur rouge et votre UsedRange s'étendra jusqu'à cette cellule qui vous semble vide.

Sub test()
ActiveSheet.UsedRange.Select
End Sub

*Même les commentaires sont inclus dans la couverture d'un UsedRange
Le mieux est d'utiliser la méthode Find pour déterminer la dernière colonne et dernière ligne.

klin89