XL 2010 Utilisation de UsedRange

cathodique · 27 Octobre 2024

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.

VB:

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.

job75 · 27 Octobre 2024

Bonsoir à tous,

J'apporte mon grain de sel en répondant strictement à la question posée au post #1.

Sur le fichier du post #1 exécutez cette macro :

VB:

Sub Contenus()
With ActiveSheet
    MsgBox .UsedRange.Address
    MsgBox Application.CountA(.Columns("K")) 'NBVAL
    MsgBox Application.CountA(.Rows(27)) 'NBVAL
End With
End Sub

La colonne K et la ligne 27 sont vides et pourtant elles sont dans le UsedRange A1:K27.

Puis celle-ci :

VB:

Sub Formats()
With ActiveSheet
    .Columns("K").ClearFormats 'efface tous les formats
    .Rows(27).ClearFormats
    MsgBox .UsedRange.Address
End With
End Sub

Le UsedRange passe à A1:J26, c'est tout à fait ce que disent mapomme et klin89..

A+

patricktoulon · 27 Octobre 2024

ben ça c'est quand on a des fichiers sales
on efface mal des cellules
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
encore un exemple qui prouve bien que l'utilisation des tableaux structurés rend les choses bien plus facile
quand on supprime une ligne on supprime tout de qui la concerne
d'ailleurs un TS rendrait cette discussion est caduque
les TS existe depuis 2007

perso je ne saurais travailler autrement aujourd'hui qu'avec des TS

crocrocro · 27 Octobre 2024

Rebonsoir,
je vais devoir lever plusieurs pouces, chronologiquement mapomme klin89 et job75.
Ma conclusion : utiliser l'une ou l'autre des options en connaissance de cause, le UsedRange étant finalement assez casse-gueule.
J'ai cherché quelle propriété de Format il y avait sur la cellule j3 : rien trouvé
3 pouces à qui me dire quelle est la propriété fatale de mon j3

mapomme · 27 Octobre 2024

crocrocro à dit:
J'ai cherché quelle propriété de Format il y avait sur la cellule j3 : rien trouvé

Ma pomme n'a rien trouvé non plus.

Cependant j'ai copié une cellule hors de la plage UsedRange pour la coller sur la plage F3:I3.
Après avoir collé cette cellule sur la plage, UsedRange donne le résultat souhaité.

De même si on n'efface que les formats sur F3:I3, on retrouve aussi le résultat souhaité.

Donc à priori c'est peut-être bien une histoire de format mais pas moyen de retrouver le contenu du format en question.

Mais ce n'est pas ça le nœud du problème. La véritable question est celle de @cathodique : Comment connaitre la dernière cellule d'une plage contenant soit une formule soit une constante ?

A priori seul le FIND semble pourvoir le faire. C'est aussi FIND qui n'est pas perturbé par le fameux et célèbre caractères nul "".

TooFatBoy · 27 Octobre 2024

crocrocro à dit:
J'ai cherché quelle propriété de Format il y avait sur la cellule j3 : rien trouvé

Pourquoi J3 ?

mapomme · 27 Octobre 2024

TooFatBoy à dit:
Pourquoi J3 ?

Mon doigt a fourchu, c'est I3.

Bon on va en rester là pour aujourd'hui. Je vais rejoindre les bras que Morphée me tends.

Bonnet de nuit à tous et à deux mains .

jurassic pork · 28 Octobre 2024

Hello,
j'ai trouvé des propriétés qui faussaient le Used Ranged pour la feuille de crocrocro en I3 ( ceux sont les alignements) avec ces bouts de code qui comparent certaines propriétés d'une cellule de référence (en dehors de la Used Ranged ) par rapport à des cellules où l'on soupçonne qu'il y a une différence de propriétés :

VB:

Sub CompareCellProperties(cell1, cell2)
    Dim prop
    Dim props As Variant
    props = Array("Font", "HorizontalAlignment", "VerticalAlignment", "WrapText", "NumberFormat")
 
    For Each prop In props
        Select Case prop
            Case "Font"
                If cell1.Font.Name <> cell2.Font.Name Or _
                   cell1.Font.Size <> cell2.Font.Size Or _
                   cell1.Font.Bold <> cell2.Font.Bold Or _
                   cell1.Font.Italic <> cell2.Font.Italic Or _
                   cell1.Font.Color <> cell2.Font.Color Then
                    Debug.Print "Diff Font : " & cell1.Address & " <> " & cell2.Address
                End If
            Case "HorizontalAlignment"
                If cell1.HorizontalAlignment <> cell2.HorizontalAlignment Then
                    Debug.Print "Diff HorAlign : " & cell1.Address & " <> " & cell2.Address
                End If
            Case "VerticalAlignment"
                If cell1.VerticalAlignment <> cell2.VerticalAlignment Then
                   Debug.Print "Diff VertAlign : " & cell1.Address & " <> " & cell2.Address
                End If
            Case "WrapText"
                If cell1.WrapText <> cell2.WrapText Then
                   Debug.Print "Diff WrapText : " & cell1.Address & " <> " & cell2.Address
                End If
            Case "NumberFormat"
                If cell1.NumberFormat <> cell2.NumberFormat Then
                    Debug.Print "Diff NumberFormat : " & cell1.Address & " <> " & cell2.Address
                End If
            ' ... et ainsi pour les autres propriétés ...
        End Select
    Next prop
End Sub

Sub FindDiff()
Dim cell As Range, cellRef As Range
Set cellRef = ActiveSheet.Range("A28")
For Each cell In ActiveSheet.Range("A27:K27")
    CompareCellProperties cell, cellRef
Next
For Each cell In ActiveSheet.Range("K1:K27")
    CompareCellProperties cell, cellRef
Next
End Sub

Il n'y a pas certaines propriétés.
Dans la feuille de cathodique , j'ai détecté des différences de format de nombre, de retour à la ligne, d'alignements.
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.

Ami calmant, J.P

cathodique · 28 Octobre 2024

job75 à dit:
Bonsoir à tous,

J'apporte mon grain de sel en répondant strictement à la question posée au post #1.

Sur le fichier du post #1 exécutez cette macro :

VB:

Sub Contenus() With ActiveSheet MsgBox .UsedRange.Address MsgBox Application.CountA(.Columns("K")) 'NBVAL MsgBox Application.CountA(.Rows(27)) 'NBVAL End With End Sub

La colonne K et la ligne 27 sont vides et pourtant elles sont dans le UsedRange A1:K27.

Puis celle-ci :

VB:

Sub Formats() With ActiveSheet .Columns("K").ClearFormats 'efface tous les formats .Rows(27).ClearFormats MsgBox .UsedRange.Address End With End Sub

Le UsedRange passe à A1:J26, c'est tout à fait ce que disent mapomme et klin89..

A+

Bonjour,

@job75: Je te remercie pour ta contribution. Cependant, chez-moi voici ce que renvoie ta seconde procédure

Comme tu peux le constater, ce n'est pas A1:J26, mais A1:K27

Bonne journée.

cathodique · 28 Octobre 2024

Bonjour @mapomme

mapomme à dit:
Mais ce n'est pas ça le nœud du problème. La véritable question est celle de @cathodique : Comment connaitre la dernière cellule d'une plage contenant soit une formule soit une constante ?

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.

VB:

DerLig = .UsedRange.Cells(.UsedRange.Cells.Count).Row

Merci beaucoup.

Bonne journée.

patricktoulon · 28 Octobre 2024

re
bonjour juste en passant
le fichier pèse 23 kilo et des cacahuètes
chez moi il donne 27 et 10 alors que cela devrait être 26 et 9
et relancer la sub et bien non l'erreur persiste
quand je parlais de fichier sales hier
c'est ce qui arrive avec les fichiers que l'on modifie a tour de bras sans prendre la peine de nettoyer tout formatage et pour peu qu'il y ai eu une erreur et que ce soit un fichier récupéré
je suis sur que si je l'ouvre en zip je vais trouver plein de cochonneries dans les XML
allez une petite sub de nettoyage je sélectionne de A1 jusqu’à tout ce que je veux garder
je nettoie
j'ai bien 26 /9

ça coute rien un coup de balais non ?
on est bon et mon fichier ne pèse plus que

VB:

Sub netoyage()
    Dim sel, cel, UR As Range
    Set sel = Selection
    If Selection Is Nothing Then Exit Sub
    If TypeName(Selection) <> "Range" Then Exit Sub
    Set UR = ActiveSheet.UsedRange
        For Each cel In UR.Cells
            If Intersect(sel, cel) Is Nothing Then
                If cel.Columns > sel.Columns.Count Then cel.EntireColumn.Delete
                If cel.Row > sel.Rows.Count Then cel.EntireRow.Delete
            End If
        Next
End Sub

voili voilou

cathodique · 28 Octobre 2024

jurassic pork à dit:

Hello,
j'ai trouvé des propriétés qui faussaient le Used Ranged pour la feuille de crocrocro en I3 ( ceux sont les alignements) avec ces bouts de code qui comparent certaines propriétés d'une cellule de référence (en dehors de la Used Ranged ) par rapport à des cellules où l'on soupçonne qu'il y a une différence de propriétés :

VB:

Sub CompareCellProperties(cell1, cell2)
    Dim prop
    Dim props As Variant
    props = Array("Font", "HorizontalAlignment", "VerticalAlignment", "WrapText", "NumberFormat")
 
    For Each prop In props
        Select Case prop
            Case "Font"
                If cell1.Font.Name <> cell2.Font.Name Or _
                   cell1.Font.Size <> cell2.Font.Size Or _
                   cell1.Font.Bold <> cell2.Font.Bold Or _
                   cell1.Font.Italic <> cell2.Font.Italic Or _
                   cell1.Font.Color <> cell2.Font.Color Then
                    Debug.Print "Diff Font : " & cell1.Address & " <> " & cell2.Address
                End If
            Case "HorizontalAlignment"
                If cell1.HorizontalAlignment <> cell2.HorizontalAlignment Then
                    Debug.Print "Diff HorAlign : " & cell1.Address & " <> " & cell2.Address
                End If
            Case "VerticalAlignment"
                If cell1.VerticalAlignment <> cell2.VerticalAlignment Then
                   Debug.Print "Diff VertAlign : " & cell1.Address & " <> " & cell2.Address
                End If
            Case "WrapText"
                If cell1.WrapText <> cell2.WrapText Then
                   Debug.Print "Diff WrapText : " & cell1.Address & " <> " & cell2.Address
                End If
            Case "NumberFormat"
                If cell1.NumberFormat <> cell2.NumberFormat Then
                    Debug.Print "Diff NumberFormat : " & cell1.Address & " <> " & cell2.Address
                End If
            ' ... et ainsi pour les autres propriétés ...
        End Select
    Next prop
End Sub

Sub FindDiff()
Dim cell As Range, cellRef As Range
Set cellRef = ActiveSheet.Range("A28")
For Each cell In ActiveSheet.Range("A27:K27")
    CompareCellProperties cell, cellRef
Next
For Each cell In ActiveSheet.Range("K1:K27")
    CompareCellProperties cell, cellRef
Next
End Sub

Il n'y a pas certaines propriétés.
Dans la feuille de cathodique , j'ai détecté des différences de format de nombre, de retour à la ligne, d'alignements.
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.

Ami calmant, J.P

Bonjour @jurassic pork ,

Je te remercie. Mais là, tu cibles des cellules bien précises.
Supposons que des cellules hors du champ visuel contiennent des données.
Et qu'on veuille connaitre la dernière cellule utilisée (ou connaitre la dernière ligne et la dernière colonne).
UsedRange n'est pas fiable.
@mapomme: préconise la fonction Find.

Merci bonne journée.

cathodique · 28 Octobre 2024

Sur le conseil de @mapomme

Mon partage perfectible.

VB:

Sub DerLig_Col_NonVide()
    Dim ws As Worksheet, DerLig As Long, DerCol As Long, Cel As Range

    Set ws = ThisWorkbook.Sheets("Feuil1")

    With ws
        Set Cel = .Cells.Find(What:="*", LookIn:=xlValues, LookAt:=xlPart, _
                                   SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
                                   MatchCase:=False)
        If Not Cel Is Nothing Then
            DerLig = Cel.Row
            MsgBox "La dernière ligne non vide est : " & DerLig
        Else
            MsgBox "Aucune donnée trouvée dans la feuille."
        End If

        Set Cel = .Cells.Find(What:="*", LookIn:=xlValues, LookAt:=xlPart, _
                                   SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, _
                                   MatchCase:=False)
        If Not Cel Is Nothing Then
            DerCol = Cel.Column
            MsgBox "La dernière colonne non vide est : " & DerCol
        Else
            MsgBox "Aucune donnée trouvée dans la feuille."
        End If
    End With
End Sub

Tu as raison @mapomme , FIND fait très bien l'affaire.
Encore Merci.
Bonne journée.

patricktoulon · 28 Octobre 2024

re
j'ai fini par trouver le bug
mais comme je suis transparent je vais le garder pour moi
je dirais juste aux plus à guéris examinez le code xml que donne debug.print [I26:J27].value(11)
une fois après avoir nettoyé avec ma sub et une autre après avoir changé le font de J27 et remis en standard

cathodique · 28 Octobre 2024

patricktoulon à dit:
re
bonjour juste en passant
le fichier pèse 23 kilo et des cacahuètes
chez moi il donne 27 et 10 alors que cela devrait être 26 et 9
et relancer la sub et bien non l'erreur persiste
quand je parlais de fichier sales hier
c'est ce qui arrive avec les fichiers que l'on modifie a tour de bras sans prendre la peine de nettoyer tout formatage et pour peu qu'il y ai eu une erreur et que ce soit un fichier récupéré
je suis sur que si je l'ouvre en zip je vais trouver plein de cochonneries dans les XML
allez une petite sub de nettoyage je sélectionne de A1 jusqu’à tout ce que je veux garder
je nettoie
j'ai bien 26 /9

Regarde la pièce jointe 1206009

ça coute rien un coup de balais non ?
on est bon et mon fichier ne pèse plus que
Regarde la pièce jointe 1206010

VB:

Sub netoyage() Dim sel, cel, UR As Range Set sel = Selection If Selection Is Nothing Then Exit Sub If TypeName(Selection) <> "Range" Then Exit Sub Set UR = ActiveSheet.UsedRange For Each cel In UR.Cells If Intersect(sel, cel) Is Nothing Then If cel.Columns > sel.Columns.Count Then cel.EntireColumn.Delete If cel.Row > sel.Rows.Count Then cel.EntireRow.Delete End If Next End Sub

voili voilou

Bonjour,

j'ai créé un fichier tout neuf. Sur mon fichier de travail j'ai sélectionné la plage A1:J26, je l'ai copié et collé dans le fichier joint.
Je me demande d'où vient la saleté que tu mentionnes?
Je suis maintenant convaincu que UsedRange est à éviter. La ligne de code ci-dessous donne un résultat erroné.
'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 !!!!

Le fichier joint n'a pas besoin de nettoyage.

Merci.

Bonne journée.

cathodique · 28 Octobre 2024

patricktoulon à dit:
re
j'ai fini par trouver le bug
mais comme je suis transparent je vais le garder pour moi
je dirais juste aux plus à guéris examinez le code xml que donne debug.print [I26:J27].value(11)
une fois après avoir nettoyé avec ma sub et une autre après avoir changé le font de J27 et remis en standard

Pourquoi deviens-tu susceptible?
Si tu as 2 cerveaux, je n'en ai qu'un et de surplus est assez vieux pour carburer à plein régime.

XL 2010 Utilisation de UsedRange

cathodique

XLDnaute Barbatruc

Pièces jointes

job75

XLDnaute Barbatruc

patricktoulon

XLDnaute Barbatruc

crocrocro

XLDnaute Impliqué

mapomme

XLDnaute Barbatruc

TooFatBoy

XLDnaute Barbatruc

mapomme

XLDnaute Barbatruc

jurassic pork

XLDnaute Occasionnel

cathodique

XLDnaute Barbatruc

cathodique

XLDnaute Barbatruc

patricktoulon

XLDnaute Barbatruc

cathodique

XLDnaute Barbatruc

cathodique

XLDnaute Barbatruc

patricktoulon

XLDnaute Barbatruc

cathodique

XLDnaute Barbatruc

cathodique

XLDnaute Barbatruc

Discussions similaires