XL 2016 VBA dernière cellule vide.

[Anto35200]

Bonjour,

Dans mon fichier extraction, j’ai le code VBA suivant qui me permet de calculer le montant de la colonne F par -1 :

Range("G2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*-1"
Range("G2").Select
Selection.AutoFill Destination:=Range("G2:G100")
Range("G2:G65").Select
ActiveWindow.SmallScroll Down:=6

Comment mettre dans ce code VBA jusqu'à la dernière cellule vide de la colonne G ?

En vous remerciant par avance de votre aide.

 

[mapomme]

Je ne sais pas si on peut avoir des cellules en erreur sans formule (avec uniquement des constantes)

Si on tape les constantes suivantes dans des cellules, la fonction ESTERREUR renvoie bien VRAI.
Constantes :

#N/A​

#VALEUR!​

#REF!​

#DIV/0!​

#NOMBRE!​

#NOM?​

#NUL!​

#PROPAGATION !​

Donc à priori, les constantes d'erreurs existent bien dans Excel.

D'ailleurs la boite de dialogue Atteindre (F5) / Cellules propose bien cette option et ça fonctionne :

 

[Dudu2]

Bien vu ! Il faut donc encore ajuster la rechercher par Match.

'----------------------------------------------------------------------
'Calcul de la dernière ligne non vide d'une colonne avec fonction Match
'Colonne: numéro ou lettre
'----------------------------------------------------------------------
Function MatchDernièreLigneEnColonne(ByVal Feuille As Worksheet, ByVal Colonne As Variant) As Long
    Const ChaineMax As String = "zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz"
    Const NombreMax As Double = (2 ^ 53 - 1) * 2 ^ 971
    Dim RngColonne As Range
    Dim RngErrors As Range
    Dim ErrorLine As Long
    Dim xlCellType As Integer
 
    Set RngColonne = Feuille.Columns(Colonne)
    
    xlCellType = xlCellTypeFormulas
    GoSub GetErrorLine
    
    xlCellType = xlCellTypeConstants
    GoSub GetErrorLine
 
    With Application
        MatchDernièreLigneEnColonne = .Max(.IfError(.Match(ChaineMax, RngColonne, 1), 0), _
                                           .IfError(.Match(NombreMax, RngColonne, 1), 0), _
                                           ErrorLine)
    End With
    Exit Function

GetErrorLine:
    On Error Resume Next
    Set RngErrors = RngColonne.SpecialCells(xlCellType, xlErrors)
    If Err.Number = 0 Then
        Set RngErrors = RngErrors.Areas(RngErrors.Areas.Count)
        ErrorLine = Application.Max(ErrorLine, RngErrors.Cells(RngErrors.Cells.Count).Row)
    End If
    On Error GoTo 0
    Return
End Function

 

[Dudu2]

Il faut faire attention au End(xlUp) ou End(xlToLeft), car ils ne prennent pas en compte la cellule de départ !

 

[Dudu2]

Alors j'ai retrouvé un vieux fichier que j'ai remis au goût du jour pour trouver:
- la dernière ligne d'une colonne,
- la dernière colonne d'une ligne.
Je ne suis pas allé jusqu'à chercher les premières.

Il y a 10 méthodes utilisées dont 6 ne fonctionnent pas en raison de leur sensibilité aux filtres et masquages et 4 qui fonctionnent toujours dont voici les résultat sur le temps d'exécution:

Note; J'ai un peu pénalisé le Scan en mettant une valeur en B100000

Pour l'affichage du message des méthodes j'ai utilisé mon nouveau Msg.Box de l'espace dont je fais ici la promo .

Les tests et le code des fonctions dans le fichier joint.

 

[Dudu2]

Certaines fonctions changent le Workbook.Saved sans qu'il y ait de réelle modification au classeur.
J'ai protégé ce flag pour les fonctions concernées dans le classeur ci-dessus mis à jour.
C'est toujours intrigant / énervant d'être prompté pour enregistrer un classeur dans lequel on n'a fait aucune modif.

 

[Dudu2]

Au final ma préférence va à la méthode par SpecialCells dont le code est le plus simple.
Code amélioré et fichier mis à jour en Post #19.

'---------------------------------------------
'Dernière ligne d'une colonne par SpecialCells
'Colonne: numéro ou lettre
'---------------------------------------------
Function SpecialCellsDernièreLigneEnColonne(ByVal Feuille As Worksheet, ByVal Colonne As Variant)
    Dim DernièreLigne As Long
    Dim WorkbookSavedAtCallTime As Boolean
    Dim TabxlCellType() As Variant
    Dim i As Integer
  
    WorkbookSavedAtCallTime = Feuille.Parent.Saved
    TabxlCellType = Array(xlCellTypeFormulas, xlCellTypeConstants)
  
    For i = LBound(TabxlCellType) To UBound(TabxlCellType)
        On Error Resume Next
        With Feuille.Columns(Colonne).SpecialCells(TabxlCellType(i))
            If Err.Number = 0 Then DernièreLigne = Application.Max(DernièreLigne, .Areas(.Areas.Count).Cells(.Areas(.Areas.Count).Rows.Count).Row)
        End With
        On Error GoTo 0
    Next i
  
    SpecialCellsDernièreLigneEnColonne = DernièreLigne
    Feuille.Parent.Saved = WorkbookSavedAtCallTime
End Function

Edit: Excellente chose que ce sujet qui m'a permis grâce à @mapomme de remettre en question certaines certitudes et identifier les solutions qui marchent.

 

[mapomme]

Re @Dudu2 ,

Je suis un peu gêné. Je n'ai fait que te signaler des cas un peu tordus qui ne fonctionnaient pas et je t'ai laissé travailler . On a maintenant une fonction qui doit couvrir tous les cas et qui laissent les filtres et lignes masquées.