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]

Petit rappel :
Le UsedRange comme son nom l'indique , c'est pour révéler les limites de le plage où il y a des choses différentes par rapport à un classeur vide. Cela peut être les valeurs, les formats, les alignements, les commentaires etc. Il faut partir d'une feuille "propre" et la garder propre pour utiliser cette plage correctement. Dans le premier classeur de cathodique ce sont bien les formats et les alignements qui n'étaient pas bons en ligne 27 et colonne K.

 

[TooFatBoy]

C'est en effet, ma véritable question.
J'ai ouvert cette discussion car je me suis rendu compte hier que la ligne de code ci-dessous, renvoie un résultat faux.

Le résultat n'est pas faux : UsedRange te donne bien la plage de cellules utilisée.
Mais ça ne correspond pas à ce que tu veux puisque tu veux la dernière cellule non vide.
Et encore, même là il faut savoir ce qu'on appelle "non vide"...

 

[klin89]

Re à tous,

Pour rebondir sur la remarque de jurassic pork au post#32, il me semble que la couverture de UsedRange est aussi affectée par les mises en forme conditionnelle, les différentes formes, shapes qui couvrent les cellules.
Personnellement, je n'ai pas testé.

klin89

 

[patricktoulon]

ah enfin !!! je plussoie @TooFatBoy
je le cite

Et encore, même là il faut savoir ce qu'on appelle "non vide"...

alors oui aujourd'hui le resultat que tu souhaite est atteint
mais que les choses soient claires ce que je m’échine a t'expliquer, c'est que ce n'est pas une solution universelle
contrairement a ce que je t'ai donné c'est adire une sub de nettoyage qui te permet de deleter tout ce qui est en dehors de ce que tu veux garder
et je dis bien DELETER!!!!! par ce qu'un clear ne changera pas la donne
c'est un bug connu depuis au moins une vingtaine d'année
le vba n'etant pas une priorité pour MS même de moins en moins d'ailleurs
il n'ont pas juger utile de corriger ce bug si tant est que ce soit possible d'ailleurs

 

[crocrocro]

Bonjour le fil,

Dans la feuille de crocrocro et celle de cathodique si on rectifie les propriétés , il faut fermer les classeurs et les rouvrir pour que le Used Range soit mis à jour.

J'avais bien passé en revue toutes les propriétés de la cellule I3 (et non J3, ma vue baisse ), et repositionné manuellement les propriétés "non standards", ici l'alignement horizontal qui était "centré" alors que par défaut, il est en bas et également l'alignement vertical (peut-être que quelqu'un me donnera la solution pour avoir par défaut "alignement horizontal centré"), l. Mais le résultat restait le même : dernière cellule utilisée : I3.
Il faut fermer et réouvrir le fichier pour que le UsedRange renvoie la bonne réponse !
Je vais noter cela dans ma liste des bizzareries Excel
à @jurassic pork
EDIT : je n'avais pas vu la remarque de @patricktoulon

heureusement excel est gentil avec vous quand vous effacez mal et que vous laissez des formats ou autres
au prochain démarrage le usedrange est correct

à lui aussi

 

[patricktoulon]

re

Il faut fermer et réouvrir le fichier pour que le UsedRange renvoie la bonne réponse !
Je vais noter cela dans ma liste des bizzareries Excel

c'est bien ce que j'ai dis hier

et c'est avec tout la bienveillance possible que je fait cette démo

je vais essayer de retrouver dans mes archives l'astuce d'evaluate ou la discussion sur DVP

 

[TooFatBoy]

J'avais bien passé en revue toutes les propriétés de la cellule I3

Rien ne dit, à priori, que c'est la cellule I3 qui pose problème ici. Ça aurait pu être une autre cellule.


En revanche, après avoir recopié une cellule vierge (par exemple W30) sur I3, on se rend compte que le UsedRange a changé, donc on sait que c'était bien I3 qui posait problème.

 

[crocrocro]

je complète mon post 36 où j'ai ajouté des pouces mérités à @patricktoulon
@patricktoulon, je viens de regarder ta vidéo :
tu fais une utilisation particulière du Find où tu mets en paramètre xlValues. Il est normal, puisqu'en E17 il y a une formule, que tu ne l'as trouves pas.
Mais comme tu le dis, il faut savoir ce que l'on cherche et donc utiliser les bons paramètres dans chaque fonction qu'on utilise
EDIT : je n'avais pas vu le post de @Jeannette qui faisait la même remarque

 

[mapomme]

Re,

Attention !

Que signifie "trouver la dernière cellule d'une plage avec valeur" quand la plage contient plusieurs lignes ou plusieurs colonnes (ou les deux) ?

Pas forcément grand chose. selon ma pomme...

Imaginons une feuille vide. Mettons une valeur en A20 et une valeur en J10.

• Cherchons la cellule avec valeur la plus basse de la feuille, on trouve A20
• Cherchons la cellule avec valeur la plus à droite de la feuille, on trouve J10

Que déduire pour l'adresse de la dernière cellule avec valeurs ? A20, J10 ou j20 ?

• Avec A20 on rate la cellule J10
• Avec J10 on rate la A20
• Avec J20 on a une cellule vide alors qu'on recherche la "dernière cellule non vide"

Donc pour tout englober sans en rater une seule (de cellule avec valeur), il faudrait plutôt choisir J20 et on se retrouve avec le problème de départ. Pour traiter les cellules, il faut encore et toujours tester chaque cellule de A1:J20 pour savoir si elle est vide ou non.

En revanche quand la plage de départ est une ligne ou une colonne, on peut parler de la dernière cellule avec valeur ou formule.

Donc j'en reviens à mon idée :
Toutes les méthodes donnent un majorant. C'est au codeur d'en tenir compte pour éliminer les cellules indésirables.
Quelles que soient les méthodes (usedrange, find, equiv, specialcells et autres), elles s'appliquent dans un contexte particulier et à mon avis il est inutile de vouloir créer une méthode générique qui sera mise à mal à chaque nouvelle version ou adaptation d'Excel. Mais si c'est pour s'amuser, pourquoi pas ?

Bon je quitte définitivement ce fil. Je ne crois pas aux anges et donc je me contrefiche de leur sexe et j'arrête d'en discuter

 

[patricktoulon]

oui jannette tu a raison
je suis parti avec leur exemple
perso j'ai mis xlformulas car ça prend aussi les chaine numérique ou texte

Sub test()
    Dim plage As Range
    'set plage=RealUsedrange(object worksheet)'méthode
    Set plage = RealUsedrange
    If plage Is Nothing Then
     
       MsgBox "cette feuille n'est pas utilisée"
    Else
        MsgBox plage.Address
    End If
End Sub

Function RealUsedrange(Optional feuille As Worksheet = Nothing) As Range
    Dim L1&, L2, C1, C2, Cel As Range
    'ordre des arguments du find ;What ,After,LookIn,LookAt,SearchOrder,SearchDirection ,MatchCase,MatchByte,SearchFormat

    If feuille Is Nothing Then Set feuille = ActiveSheet

    With feuille
        'debut du usedrange
        'recherche par ligne a partir du debut
        Set Cel = .Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlNext, False)
        If Not Cel Is Nothing Then L1 = Cel.Row Else L1 = 1

        'recherche par colonne a partir du debut
        Set Cel = .Cells.Find("*", , xlFormulas, xlPart, xlByColumns, xlNext, False)
        If Not Cel Is Nothing Then C1 = Cel.Column Else C1 = 1
        '----------------------------------------------------------------------
        'fin du usedrange
        'recherche par ligne depuis la fin
        Set Cel = .Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious, False)
        If Not Cel Is Nothing Then L2 = Cel.Row Else L2 = 1

        'recherche par colonne depuis la fin
        Set Cel = .Cells.Find("*", , xlFormulas, xlPart, xlByColumns, xlPrevious, False)
        If Not Cel Is Nothing Then C2 = Cel.Column Else C2 = 1

        If L1 + L2 + C1 + C2 = 4 Then
            Set RealUsedrange = Nothing
        Else
            Set RealUsedrange = .Range(.Cells(L1, C1), .Cells(L2, C2))
        End If

    End With
End Function

il faudrait que je teste l'argument vide dans toutes les conditions

 

[patricktoulon]

Ok testé sans arguments c'est bon ça prend tout
maintenant il faut faire attention a ne pas laisser une feuille sales de toute valeur"" en dehors des tableaux

Sub test()
    Dim plage As Range
    'set plage=RealUsedrange(object worksheet)'méthode
    Set plage = RealUsedrange
    If plage Is Nothing Then
      
       MsgBox "cette feuille n'est pas utilisée"
    Else
        MsgBox plage.Address
    End If
End Sub

Function RealUsedrange(Optional feuille As Worksheet = Nothing) As Range
    Dim L1&, L2, C1, C2, Cel As Range
    'ordre des arguments du find ;What ,After,LookIn,LookAt,SearchOrder,SearchDirection ,MatchCase,MatchByte,SearchFormat

    If feuille Is Nothing Then Set feuille = ActiveSheet

    With feuille
        'debut du usedrange
        'recherche par ligne a partir du debut
        Set Cel = .Cells.Find("*", , , , xlByRows, xlNext, False)
        If Not Cel Is Nothing Then L1 = Cel.Row Else L1 = 1

        'recherche par colonne a partir du debut
        Set Cel = .Cells.Find("*", , , , xlByColumns, xlNext, False)
        If Not Cel Is Nothing Then C1 = Cel.Column Else C1 = 1
        '----------------------------------------------------------------------
        'fin du usedrange
        'recherche par ligne depuis la fin
        Set Cel = .Cells.Find("*", , , , xlByRows, xlPrevious, False)
        If Not Cel Is Nothing Then L2 = Cel.Row Else L2 = 1

        'recherche par colonne depuis la fin
        Set Cel = .Cells.Find("*", , , , xlByColumns, xlPrevious, False)
        If Not Cel Is Nothing Then C2 = Cel.Column Else C2 = 1

        If L1 + L2 + C1 + C2 = 4 Then
            Set RealUsedrange = Nothing
        Else
            Set RealUsedrange = .Range(.Cells(L1, C1), .Cells(L2, C2))
        End If

    End With
End Function

 

[patricktoulon]

re
@Jeannette

De Laurent Longre, en 2000 : (mais je pense que son code de nettoyage est bien connu de tout le monde)

ben non moi je ne le connais pas
pour moi le netoyage consiste a DELETER toute ligne et colonne après le(s) tableau(x)
car aucune fonction vba ou excel ne supprime vraiment
mais je veux bien le connaitre je suis très intéressé et très curieux

 

[mapomme]

Re @patricktoulon,

Voici son code (je crois) :

Sub NettoieEtDerniereCellule()     ' Laurent Longre 2000
Dim Sht As Worksheet, DCell As Range, Calc As Long, Rien As String
On Error Resume Next
Calc = Application.Calculation
With Application
  .Calculation = xlCalculationManual
  .StatusBar = "Nettoyage en cours..."
  .EnableCancelKey = xlErrorHandler
  .ScreenUpdating = False
End With
For Each Sht In Worksheets
  If Sht.UsedRange.Address <> "$A$1" Or Not IsEmpty(Sht.[A1]) Then
    Set DCell = Sht.Cells.Find("*", , , , xlByRows, xlPrevious)(2)
    If Not DCell Is Nothing Then
      Sht.Range(DCell, Sht.Cells([A:A].Count, 1)).EntireRow.Clear
      Set DCell = Nothing
      Set DCell = Sht.Cells.Find("*", , , , xlByColumns, xlPrevious)(, 2)
      If Not DCell Is Nothing Then _
         Sht.Range(DCell, Sht.[IV1]).EntireColumn.Clear
    End If
    Rien = Sht.UsedRange.Address
  End If
Next Sht
Application.StatusBar = False
Application.Calculation = Calc
End Sub

 

[patricktoulon]

ok ca fonctionne
merci @Jeannette
bon ben voila jusqu’à ce qu'on découvre une nouvelle entourloupe
on a une fonction RealUsedrange
et une fonction de nettoyage

 

[Modeste geedee]

re
@Jeannette

ben non moi je ne le connais pas
pour moi le netoyage consiste a DELETER toute ligne et colonne après le(s) tableau(x)
car aucune fonction vba ou excel ne supprime vraiment
mais je veux bien le connaitre je suis très intéressé et très curieux

Bonsour,
,
15 ans déjà, que dis-je : 20 ans...
.en haut à droite : la loupe !
Tu tapes: nettoie.xls
Et voili...