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.

 

[Compte Supprimé 979]

Bonsoir

Pourtant dit dans la charte

Demandeur​

2.1 – Un outil de recherche sur le forum permet de voir si la question a déjà été posée. Ne pas hésiter à l’utiliser.

Résultats de la recherche pour la requête: dernière ligne non vide

excel-downloads.com

 

[Phil69970]

Bonjour à tous

@Anto35200

Il n'y a pas de macro dans ton fichier !!!

j’ai le code VBA suivant

Se servir de l'enregistreur est souvent une bonne idée mais après il faut faire le ménage
Voici ton code nettoyé

Range("G2").FormulaR1C1 = "=RC[-1]*-1"
Range("G2").AutoFill Destination:=Range("G2:G100")

Et pour répondre à ta question je rejoins Bruno un minimum de recherche de ta part est souhaitable

Et une fois que tu auras trouvé sur les centaines voir milliers de réponses
Tu pourrais avoir ceci

Dim Derlig& 'Declararation des variables
Derlig=XXX. '<== c'est ce que tu vas chercher
Range("G2").FormulaR1C1 = "=RC[-1]*-1"
Range("G2").AutoFill Destination:=Range("G2:G" & Derlig)

Merci de ton retour ..... complété de la bonne réponse je n'en doute pas

 

[mapomme]

Bonsoir @Anto35200,

Une fonction VBA qu'on peut utiliser dans un code VBA ou sur une feuille de calcul.
Utilisation : =derLigne(xcell) où xcell est n'importe quelle cellule de la colonne à traiter.
Exemple : =derLigne(range("A1")) dans un code ou =derLigne(A1) sur une feuille de calcul.

nota :

• on prend en compte les lignes masquées ou filtrées
• on prend en compte les tableaux structurés
• on ignore les cellules avec le fameux caractère nul

la fonction retourne 0 si la colonne est vide.

Mettre le code dans un module indépendant :

Function derLigne&(xcell As Range)
Dim der&, t, i&
   With xcell.Parent
      On Error Resume Next
      der = .UsedRange.Row + .UsedRange.Rows.Count - 1
      If der > 0 Then
         t = .Cells(1, xcell.Column).Resize(der).Formula
         For i = der To 1 Step -1
            If Len(t(i, 1)) <> 0 Then Exit For
         Next i
         If i >= 1 Then derLigne = i
      End If
      On Error GoTo 0
   End With
End Function

On a ajouté un code lié au bouton Hop! pour placer les formules :

Sub Formules()
Dim N&
   With Sheets("Sheet")
      N = derLigne(.Range("f1"))
      .Range("g2:g" & Rows.Count).ClearContents
      If N > 1 Then .Range("g2:g" & N).FormulaR1C1 = "=-RC[-1]"
   End With
End Sub

 

[TooFatBoy]

Bonjour,

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

Avec Excel 2016 la dernière cellule vide d'une colonne est quasiment systématiquement sur la ligne 1048576, donc pour toi ce serait la cellule G1048576.

 

[Dudu2]

Bonjour,
Voilà ce que j'utilise pour la dernière ligne valorisée d'une colonne.
Si tu cherches la cellule vide qui suit, il faut décaler de 1 ligne si pas Rows.Count (1048576).

<> Soit avec xlUp (mais attention, il faut dé-filtrer la feuille)

'Défiltrer pour ne pas fausser le xlUp avec des lignes de fin filtrées
With Activesheet
    If Not .AutoFilter Is Nothing Then .AutoFilter.ShowAllData
    DernièreLigne = .Cells(Rows.Count, NoColonne).End(xlUp).Row
End With

<> Soit avec une fonction:

'----------------------------------------------------------------------
'Calcul de la dernière ligne non vide d'une colonne avec fonction Match
'DernièreLigne = DernièreLigneEnColonne(ActiveSheet, 1)
'----------------------------------------------------------------------
Function DernièreLigneEnColonne(ByVal Feuille As Worksheet, ByVal NuméroColonne As Long) As Long
    Const ChaineMax as String = "zzzzzzzzzzzzzzzzzzzz"
    Const NombreMax as Double = (2 ^ 53 - 1) * 2 ^ 971
    Dim Colonne As Range
 
    Set Colonne = Feuille.Columns(NuméroColonne)
 
    With Application
        DernièreLigneEnColonne = .Max(.IfError(.Match(ChaineMax, Colonne, 1), 0), _
                                      .IfError(.Match(NombreMax, Colonne, 1), 0))
    End With
End Function

Après j'ai plus sophistiqué pour rechercher toujours avec la fonction
- en ignorant les valeurs de chaine vide en tant que valeurs constantes
- en ignorant les valeurs de chaine vide en tant que valeurs de formules
Mais je ne pense pas que tu veuilles aller jusque là.

 

[Dudu2]

D'ailleurs, je ne suis pas sûr que ma constante Const ChaineMax as String = "zzzzzzzzzzzzzzzzzzzz" soit idéale.
Faudrait que je fasse plutôt un String(32, chr(255)) ou quelque chose comme ça.
Après Unicode je sais pas comment ça se goupille avec les String VBA.

 

[mapomme]

@Dudu2 ,

Attention !

• Aucun des deux Match ne détecte par exemple la valeur ( =NA() ) pour dernière cellule d'une colonne
• End(xlUp) ne tient pas compte des lignes masquées
• End(xlUp) montre des anomalies sur les tableaux structurés (END(xlUP) détecte toujours la dernière ligne du tableau (vide ou non) comme étant la dernière cellule).

 

[TooFatBoy]

End(xlUp) montre des anomalies sur les tableaux structurés (END(xlUP) détecte toujours la dernière ligne du tableau (vide ou non) comme étant la dernière cellule).

It's not a bug, it's a feature

 

[mapomme]

It's not a bug, it's a feature

Pas d'accord du tout. On voit bien que les TS sont une verrue ajoutée à Excel (belle verrue mais verrue malgré tout). A mon avis c'était principalement pour l'interface avec des programmes externes de type PQ. Son intégration à Excel montre des failles (fonctionnement de END(), méthode pour utiliser des références absolues d'en-têtes de colonnes...)

En tout cas, les TS perturbent le fonctionnement normal et intuitif de END().

 

[Dudu2]

@mapomme,
Merci pour ces précisions. Donc pour xlUp:

'Défiltrer et démasquer pour ne pas fausser le xlUp avec des lignes de fin filtrées ou masquées
With ActiveSheet
    If Not .AutoFilter Is Nothing Then .AutoFilter.ShowAllData
    .Cells.EntireRow.Hidden = False
    DernièreLigne = .Cells(Rows.Count, NoColonne).End(xlUp).Row
    If IsEmpty(.Cells(DernièreLigne, NoColonne)) Then DernièreLigne = 0
End With

 

[Dudu2]

Quant aux TS, c'est en effet spécial. Les cellules sont "réservées".
Je ne sais pas ce qu'il faut en penser. Peut-être faudrait-il remonter la colonne si on est dans ce cas ?

 

[Dudu2]

Sinon, avec les TS ça se complique car la ligne d'en-têtes peut être affichée ou non.
Si elle n'est pas affichée, ça bloque sur la 1ère ligne du TS. Il faut donc continuer à détecter.
Cette fonction semble marcher dans tous les cas.

Sub a()
    MsgBox xlUpDernièreLigneEnColonne(ActiveSheet, ActiveCell.Column)
    'MsgBox MatchDernièreLigneEnColonne(ActiveSheet, ActiveCell.Column)
End Sub

'----------------------------
'Dernière ligne d'une colonne
'Colonne: numéro ou lettre
'----------------------------
Function xlUpDernièreLigneEnColonne(ByVal Feuille As Worksheet, ByVal Colonne As Variant) As Long
    Dim DernièreLigne As Long
    Dim Tbl As ListObject
  
    With ActiveSheet
        'Défiltrer et démasquer pour ne pas fausser le xlUp avec des lignes de fin filtrées ou masquées
        If Not .AutoFilter Is Nothing Then .AutoFilter.ShowAllData
        .Cells.EntireRow.Hidden = False
        DernièreLigne = .Cells(Rows.Count, Colonne).End(xlUp).Row
      
        For Each Tbl In .ListObjects
            If Not Intersect(.Cells(DernièreLigne, Colonne), Tbl.Range) Is Nothing Then Exit For
        Next Tbl
      
        If Not Tbl Is Nothing Then
            Do While DernièreLigne > 1 And IsEmpty(.Cells(DernièreLigne, Colonne))
                DernièreLigne = .Cells(DernièreLigne, Colonne).End(xlUp).Row
            Loop
        End If

        If IsEmpty(.Cells(DernièreLigne, Colonne)) Then DernièreLigne = 0
    End With
  
    xlUpDernièreLigneEnColonne = DernièreLigne
End Function

Edit: mais bon, ça casse les filtres et les masquages, pas terrible !

 

[mapomme]

Re @Dudu2,

Pour l'instant, le code que j'ai proposé (un peu bestial il faut le reconnaître) fonctionne sans afficher les lignes cachées, les lignes filtrées et même en présence de TS.

 

[Dudu2]

Bonjour TLM,
@mapomme, oui, en parcourant les valeurs de cellules tu ne peux pas te tromper.
Suite à ta remarque j'ai revu le code par Match.
Je ne sais pas si on peut avoir des cellules en erreur sans formule (avec uniquement des constantes). Je ne crois pas mais...

'----------------------------------------------------------------------
'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 RngFormulaErrors As Range
    Dim MaxErrorLine As Long
 
    Set RngColonne = Feuille.Columns(Colonne)
   
    On Error Resume Next
    Set RngFormulaErrors = RngColonne.SpecialCells(xlCellTypeFormulas, xlErrors)
    If Err.Number = 0 Then
        Set RngFormulaErrors = RngFormulaErrors.Areas(RngFormulaErrors.Areas.Count)
        MaxErrorLine = RngFormulaErrors.Cells(RngFormulaErrors.Cells.Count).Row
    End If
    On Error GoTo 0
 
    With Application
        MatchDernièreLigneEnColonne = .Max(.IfError(.Match(ChaineMax, RngColonne, 1), 0), _
                                           .IfError(.Match(NombreMax, RngColonne, 1), 0), _
                                           MaxErrorLine)
    End With
End Function