XL 2010 Utilisation de UsedRange

cathodique

cathodique

XLDnaute Barbatruc
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.
 

Pièces jointes

  • DerLig_Dercol_UsedRange.xlsm
    16.2 KB · Affichages: 21
Trier par date Trier par votes
job75

job75

XLDnaute Barbatruc
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+
 
Vote positif 0 Vote négatif
patricktoulon

patricktoulon

XLDnaute Barbatruc
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
 
Vote positif 0 Vote négatif
C

crocrocro

XLDnaute Impliqué
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
 
Vote positif 0 Vote négatif
mapomme

mapomme

XLDnaute Barbatruc
Supporter XLD
crocrocro à dit:
J'ai cherché quelle propriété de Format il y avait sur la cellule j3 : rien trouvé :(
Cliquez pour agrandir...
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 "".
 
Dernière édition:
Vote positif 0 Vote négatif
jurassic pork

jurassic pork

XLDnaute Occasionnel
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
 
Dernière édition:
Vote positif 0 Vote négatif
cathodique

cathodique

XLDnaute Barbatruc
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+
Cliquez pour agrandir...
Bonjour,

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

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

Bonne journée.
 
Vote positif 0 Vote négatif
cathodique

cathodique

XLDnaute Barbatruc
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 ?
Cliquez pour agrandir...
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.
 
Vote positif 0 Vote négatif
patricktoulon

patricktoulon

XLDnaute Barbatruc
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


demo1.gif


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


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
 
Vote positif 0 Vote négatif
cathodique

cathodique

XLDnaute Barbatruc
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
Cliquez pour agrandir...
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.
 
Vote positif 0 Vote négatif
cathodique

cathodique

XLDnaute Barbatruc
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.
 
Vote positif 0 Vote négatif
patricktoulon

patricktoulon

XLDnaute Barbatruc
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
 
Vote positif 0 Vote négatif
cathodique

cathodique

XLDnaute Barbatruc
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
Cliquez pour agrandir...
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.
 
Vote positif 0 Vote négatif
cathodique

cathodique

XLDnaute Barbatruc
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
Cliquez pour agrandir...
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.
 
Vote positif 0 Vote négatif

Discussions similaires

Nicolas JACQUIN
  • Question
XL 2021 Exportation feuille msgbox yes no avec module Thisworkbook
2
Réponses
21
Affichages
1 K
Nicolas JACQUIN
Nicolas JACQUIN
Nicolas JACQUIN
  • Question
XL 2019 Fusionner jusqu'à dernière colonne contenant
Réponses
8
Affichages
633
TooFatBoy
TooFatBoy
chris6999
  • Question
Microsoft 365 Boucle VB pour supprimer les caractères spéciaux et numériques d'une chaîne
Réponses
5
Affichages
389
patricktoulon
patricktoulon
A
  • Question
Insérer un copier / coller
Réponses
0
Affichages
292
aziz
A
A
  • Question
VBA qui fonctionnait avant, mais ne fonctionne plus avec le 64 bits
Réponses
2
Affichages
132
patricktoulon
patricktoulon
Ferbank
  • Question
XL 2019 modifier une macro
Réponses
2
Affichages
968
Ferbank
Ferbank
cathodique
  • Résolu(e)
XL 2010 Userform plein écran sans barre de titre
Réponses
7
Affichages
413
cathodique
cathodique
E
  • Résolu(e)
XL 2019 transfer listbox vers une colonne à partir de la 4 ligne
Réponses
2
Affichages
135
escudo
E
G
  • Question
Microsoft 365 Export données
Réponses
4
Affichages
448
GClaire
G
cathodique
  • Résolu(e)
XL 2010 Mauvaise utilisation de la fonction InStr
Réponses
11
Affichages
615
jurassic pork
jurassic pork

Membres actuellement en ligne

2 membres et 65 invités.

Partager cette page

Tutos vidéos

Power Query : Ajouter des requêtes (Excel et Power BI)
Créez votre première fonction LAMBDA dans Excel en 3 étapes!
LAMBDA, l’arme secrète de Microsoft pour Excel !
Office Insider - Découvrez les nouveautés Excel avant tout le monde

Statistiques des forums

Discussions
315 064
Messages
2 115 861
Membres
112 602
dernier inscrit
annouara