Trouver la dernière cellule non vide en VBA

D

Djorge84

XLDnaute Nouveau
Bonjour,

mon problème doit être simple à régler je pense, encore que pour ma part je ne vois pas comment... :confused:

Je désire trouver la dernière cellule non vide dans une plage donnée ("F10:F20").
Je pensais utiliser pour cela End(xlDown).Row, mais cela ne me donne pas ce que je veux car les cellules de la plage considérée contiennent en fait une formule Excel avec une condition If qui leur attribue la valeur "" dans certains cas...

Je me rends donc compte qu'elles ne sont pas vide au sens de End(xlDown).Row, qui me renvoie 20 (logique au final...) :p

Quelqu'un pourrait me dire comment je peux m'en sortir avec ce problème?

Merci par avance!
 
Trier par date Trier par votes
D

Djorge84

XLDnaute Nouveau
Re : Trouver la dernière cellule non vide en VBA

Bonjour 13GIBE59,

oui essayé également. Le problème est le même car il ne "voit" pas les cellules sans valeur comme vide du fait de la formule Excel qui leur impose "" sous certaines conditions...
 
Vote positif 0 Vote négatif
Dranreb

Dranreb

XLDnaute Barbatruc
Re : Trouver la dernière cellule non vide en VBA

Bonjour.
À essayer:
VB: 
Function PlageÀPartirDe(ByVal PlageDép As Range) As Range
Dim F As Worksheet, LDéb As Long, CDéb As Long, LMax As Long, CMax As Long, Plg As Range
Set F = PlageDép.Worksheet: LDéb = PlageDép.Row: CDéb = PlageDép.Column
With F.UsedRange: LMax = .Row + .Rows.Count - 1: CMax = .Column + .Columns.Count - 1: End With
Do:
'   If IsEmpty(F.Cells(LMax, CMax).Value) Then
'   If F.Cells(LMax, CMax).Value = "" Then
'      Set Plg = F.Cells(LMax, CMax).End(xlToLeft)
'      If Plg.Column >= CDéb And Not IsEmpty(Plg.Value) Then Exit Do
'   Else: Exit Do
'      End If
   If WorksheetFunction.CountIf(F.Cells(LDéb, CDéb).Resize(LMax - LDéb + 1), "") < LMax - LDéb + 1 Then Exit Do
   LMax = LMax - 1: Loop Until LMax < LDéb
Do:
'   If IsEmpty(F.Cells(LMax, CMax).Value) Then
'      Set Plg = F.Cells(LMax, CMax).End(xlUp)
'      If Plg.Row >= LDéb And Not IsEmpty(Plg.Value) Then Exit Do
'   Else: Exit Do
'      End If
   If WorksheetFunction.CountIf(F.Cells(LDéb, CDéb).Resize(, CMax - CDéb + 1), "") < CMax - CDéb + 1 Then Exit Do
   CMax = CMax - 1: Loop Until CMax < CDéb
If LMax < LDéb Or CMax < CDéb Then Set PlageÀPartirDe = Nothing: Exit Function
Set PlageÀPartirDe = PlageDép.Resize(LMax - LDéb + 1, CMax - CDéb + 1)
End Function
À+
 
Vote positif 0 Vote négatif
C

CHALET53

XLDnaute Barbatruc
Re : Trouver la dernière cellule non vide en VBA

Bonjour,

une formule que j'avais trouvé sur ce site (merci à l'auteur : peut-être Monique)

=MAX(SI(ESTNA(EQUIV(9^9;A:A));"";EQUIV(9^9;A:A));SI(ESTNA(EQUIV("zz";A:A));"";EQUIV("zz";A:A)))
a+
 
Vote positif 0 Vote négatif
S

Si...

XLDnaute Barbatruc
Re : Trouver la dernière cellule non vide en VBA

Salut

Si... VBA, peut-être ainsi ?
Code: 
Option Explicit
'dans le module de la feuille, à adapter
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error Resume Next
    Application.EnableEvents = False
    Dim Ou As String, Dli As Long
    Ou = Selection.SpecialCells(xlCellTypeConstants).Address
    Dli = Right(Ou, Len(Ou) - InStrRev(Ou, "$"))
    MsgBox Dli 'pour voir
    Application.EnableEvents = True
End Sub
 
Vote positif 0 Vote négatif
D

Djorge84

XLDnaute Nouveau
Re : Trouver la dernière cellule non vide en VBA

CHALET53 à dit:
Bonjour,

une formule que j'avais trouvé sur ce site (merci à l'auteur : peut-être Monique)

=MAX(SI(ESTNA(EQUIV(9^9;A:A));"";EQUIV(9^9;A:A));SI(ESTNA(EQUIV("zz";A:A));"";EQUIV("zz";A:A)))
a+
Cliquez pour agrandir...

merci à vous 4 pour avoir pris de votre temps pour m'apporter des éléments de réponse.

Je précise à l'attention de CHALET53 que je veux faire ça en VB...

Sinon pour les autres, j'essaie de comprendre ce que vous m'avez proposé, et c'est pas évident vu mon niveau en VB... Mais je m'accroche.

J'avoue quand même que si l'utilisation de Find permet de résoudre mon problème, ça a l'ar plus simple. Encore faut-il que je réussisse à l'adapter à mon cas :)
 
Vote positif 0 Vote négatif
D

Djorge84

XLDnaute Nouveau
Re : Trouver la dernière cellule non vide en VBA

Dranreb à dit:
Bonjour.
À essayer:
VB: 
Function PlageÀPartirDe(ByVal PlageDép As Range) As Range
Dim F As Worksheet, LDéb As Long, CDéb As Long, LMax As Long, CMax As Long, Plg As Range
Set F = PlageDép.Worksheet: LDéb = PlageDép.Row: CDéb = PlageDép.Column
With F.UsedRange: LMax = .Row + .Rows.Count - 1: CMax = .Column + .Columns.Count - 1: End With
Do:
'   If IsEmpty(F.Cells(LMax, CMax).Value) Then
'   If F.Cells(LMax, CMax).Value = "" Then
'      Set Plg = F.Cells(LMax, CMax).End(xlToLeft)
'      If Plg.Column >= CDéb And Not IsEmpty(Plg.Value) Then Exit Do
'   Else: Exit Do
'      End If
   If WorksheetFunction.CountIf(F.Cells(LDéb, CDéb).Resize(LMax - LDéb + 1), "") < LMax - LDéb + 1 Then Exit Do
   LMax = LMax - 1: Loop Until LMax < LDéb
Do:
'   If IsEmpty(F.Cells(LMax, CMax).Value) Then
'      Set Plg = F.Cells(LMax, CMax).End(xlUp)
'      If Plg.Row >= LDéb And Not IsEmpty(Plg.Value) Then Exit Do
'   Else: Exit Do
'      End If
   If WorksheetFunction.CountIf(F.Cells(LDéb, CDéb).Resize(, CMax - CDéb + 1), "") < CMax - CDéb + 1 Then Exit Do
   CMax = CMax - 1: Loop Until CMax < CDéb
If LMax < LDéb Or CMax < CDéb Then Set PlageÀPartirDe = Nothing: Exit Function
Set PlageÀPartirDe = PlageDép.Resize(LMax - LDéb + 1, CMax - CDéb + 1)
End Function
À+
Cliquez pour agrandir...

Bonjour et merci pour votre proposition.
Faut-il prendre en compte les lignes de commentaires? Parce que j'avoue que j'ai du mal à saisir l'intégralité de ce que fait ce bout de code.
 
Vote positif 0 Vote négatif
Dranreb

Dranreb

XLDnaute Barbatruc
Re : Trouver la dernière cellule non vide en VBA

J'ai volontairement laissé les lignes en commentaires car c'est une ébauche d'une procédure générale qui pourrait vous intéresser (ou un autre) pour trouver la plage utilisée même au sens où la méthode End peut y aider. Si celà ne vous intéresse pas, ce que je comprendrais tout à fait, virez les purement et simplement.
C'est un problème récurent sur ce forum et en pratique. C'est comme pour les remèdes contre le rhum, le fait qu'il y en a tant prouve qu'aucun n'est vraiment efficace. End se croit intelligent de déduire ce qu'on veut du fait que la cellule spécifiiée est déjà vide ou pas, CurrentRegion dépend aussi de la cellule de départ et arrête les frais avant les premières ligne et colonne vides trouvées, SpecialCellTypeLastCell garde la mémoire de la dernière cellule un jour occupée jadis jusqu'à ce qu'on supprime les lignes inutiles et enregistre le classeur, et enfin UsedRange considère comme utilisées les cellules munies ... de bordures !
Bref, rien qui marche irréprochablement sans être obligé de reprendre derrière.
Bon alors, pour ne pas bouffer trop de temps cpu en partant de rien, on va partir de celui qui minimise les risque de louper quelque chose, UsedRange et on élague dabord les dernière lignes quand même vides selon notre critère. Pareil ensuite pour les colonnes quand même vides.
Notre critère: le nombre de cellule vallant une chaîne vide est égal au nombre de cellules.
CoutIf est le nom natif de la fonction Excel NB.SI
Donc dans une expression formule pour la dernière ligne de ma plage momentanément retenue ma condition serait:
NB.SI(MaLigne;"")=COLONNES(MaLigne)
Alors ce n'est pas ma dernière ligne et je décrémente celle ci avant de recommencer avec la précédente jusqu'à éviter de ramer même sur la falaise.
J'espère que mon code vous paraît à présent plus clair.
Si quelqu'un a mieux je suis preneur.
À+
 
Dernière édition:
Vote positif 0 Vote négatif
D

Djorge84

XLDnaute Nouveau
Re : Trouver la dernière cellule non vide en VBA

Merci de ces précisions! :)

Pour info à force de chercher, je viens de trouver ça qui détecte bien le fait que ma cellule "F10" n'affiche rien alors qu'elle comporte bien une formule conditionnelle du type If(test OK, blabla, "").

Il s'agit de vérifier

Code: 
If Range("F10") = vbNullString Then MsgBox "Cellule vide"

Et là j'ai bien le message voulu pour les cellules où rien n'apparait, contrairement à ce que j'obtiens avec IsEmpty.

Je me dis qu'à partir de ça, je dois pouvoir également résoudre mon problème, mais je n'y suis pas encore arrivé...
 
Vote positif 0 Vote négatif
D

Djorge84

XLDnaute Nouveau
Re : Trouver la dernière cellule non vide en VBA

Bonjour,

pour précision ce code donne bien ce que je veux : :)

Code: 
Sub sExempleTest1()

Dim i, j, k, l, m, n As Integer
Dim Ind_L As Integer

    If Sheets("Feuille1").Range("F10") = vbNullString Then
    i = 0
    Else:    i = Sheets("Feuille1").Range("F10").Row
    End If

    If Sheets("Feuille1").Range("F11") = vbNullString Then
    j = 0
    Else:    j = Sheets("Feuille1").Range("F11").Row
    End If
   
    If Sheets("Feuille1").Range("F12") = vbNullString Then
    k = 0
    Else:    k = Sheets("Feuille1").Range("F12").Row
    End If
   
    If Sheets("Feuille1").Range("F13") = vbNullString Then
    l = 0
    Else:    l = Sheets("Feuille1").Range("F13").Row
    End If

    If Sheets("Feuille1").Range("F14") = vbNullString Then
    m = 0
    Else:    m = Sheets("Feuille1").Range("F14").Row
    End If

    If Sheets("Feuille1").Range("F15") = vbNullString Then
    n = 0
    Else:    n = Sheets("Feuille1").Range("F15").Row
    End If
    
Ind_L = Application.WorksheetFunction.Max(i, j, k, l, m, n)

MsgBox Ind_L

End Sub


sauf que bien sûr ça serait beaucoup mieux avec une boucle FOR... que je n'arrive pas à réaliser...

Code: 
Sub sExempleTest2()

Dim i As Integer
Dim k As Integer

i = 0

For k = 10 To 20
 
    If Sheets("Feuille1").Range(Cells(k, 6)) = vbNullString Then
    
    Else:    i = k
  
            Exit For
    End If
Next k

MsgBox i

End Sub

Il semble qu'il y ait une erreur d'exécution 1004 au niveau du If... :confused:

Quelqu'un aurait une idée?
 
Vote positif 0 Vote négatif
Dranreb

Dranreb

XLDnaute Barbatruc
Re : Trouver la dernière cellule non vide en VBA

Bonjour
Range(Cells(k,6)) Cherche à représenter une plage dont la référence ou le nom est contenu dans la valeur de la cellule ligne k, colonne 6. Enlevez le Range(..) autour.
Mais dois-je en comprendre que ma procédure ne marche pas ?
Alors, s'il vous plait, dites moi pourquoi car je veux la mettre au point.
À+
 
Vote positif 0 Vote négatif
D

Djorge84

XLDnaute Nouveau
Re : Trouver la dernière cellule non vide en VBA

Merci!

le code suivant fonctionne donc : :cool:

Code: 
Sub sExempleTest2()

Dim i As Integer
Dim k As Integer

i = 0

For k = 0 To 10
 
    If Sheets("Fiche Panier").Cells(10 + k, 6) = vbNullString Then
    
    i = (10 + k) - 1
     
            Exit For
    End If
Next k

MsgBox i

End Sub

Je vous remercie tous pour m'avoir filer un coup de main depuis hier!

Bonne journée
 
Vote positif 0 Vote négatif

Discussions similaires

Usine à gaz
  • Résolu(e)
XL 2021 Sélectionner plusieurs plages jusqu'à la dernière ligne NON vide en col "E"
Réponses
5
Affichages
69
Usine à gaz
Usine à gaz
S
  • Question
Microsoft 365 Référer à la première cellule non vide en haut
Réponses
3
Affichages
147
ShrekL
S
L
  • Résolu(e)
Microsoft 365 Suppression de cellules vide
Réponses
3
Affichages
187
Le Rouky
L
P
  • Résolu(e)
Microsoft 365 vérifier si Plage de cellules non vide
Réponses
2
Affichages
369
pat66
P
Usine à gaz
  • Résolu(e)
XL 2021 VBA sélectionner 10 lignes sous dernière cellule colonn A NON VIDE
2
Réponses
20
Affichages
673
TooFatBoy
TooFatBoy
A
  • Résolu(e)
XL 2016 Selectionner plages de cellule
Réponses
3
Affichages
85
gbinforme
G
A
  • Résolu(e)
XL 2016 VBA pour sélectionner une plage de cellule à la dernière cellule vide
Réponses
2
Affichages
405
Anto35200
A
G
  • Résolu(e)
XL 2013 copier coller valeur d'une colonne dynamique
Réponses
13
Affichages
341
gwenn49
G
V
  • Question
XL 2016 Fonction si avec cellule vide et cellule non vide
Réponses
3
Affichages
517
vinie74
V
VBA_dev_Anne_Marie
  • Question
Microsoft 365 VBA :trouver une première ligne non vide dans une matrice
Réponses
12
Affichages
1 K
Deadpool_CC
Deadpool_CC

Membres actuellement en ligne

3 membres et 103 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
312 294
Messages
2 086 895
Membres
103 404
dernier inscrit
sultan87