Comment trouver la dernière cellule ? Pièges avec les formules

[philest]

Bonjour à tous,

Je sèche depuis un bon moment. Je sais utiliser du VBA pour trouver la dernière cellule d'une colonne A par exemple : MsgBox [CountA(A1:A65536)] ou Application.WorksheetFunction.CountA(Range("A:A")) ou DerniereLigne = Range("A1").End(xlDown).Address etc...

Le piège c'est lorsque les cellules vides d'une colonne (sans texte, ni nombres) ont toutes une formule incluse . Le résultat est faussé car les 3 méthodes citées plus haut vont données : j'ai trouvé la dernière cellule avec une formule incluse. Or je veux trouver la dernière cellule qui contient du texte ou des nombres. Suis-je clair ? un exemple concret. j'ai une colonne A . Depuis A1 jusqu'à A10 j'insère à chaque cellule la formule "=SI(ESTVIDE(B1).....etc" j'ai écrit du texte depuis A1 jusqu'a A7. si en VBA je lui demande de me trouver la dernière cellule, je devrais m'attendre au résultat A7. Or il va me donner A10 qui est une cellule vide. Comment faire pour trouver la dernière cellule malgré cette contrainte ?

merci

 

[ROGER2327]

Re : Comment trouver la dernière cellule ? Pièges avec les formules

Re…

(…)
Après quelques tests, les macros de Jean-Marcel et MJ13 conduisent aux mêmes résultats mais ce ne sont pas toujours ceux des autres macros proposées.

Est-il possible d'avoir un exemple montrant que ma proposition donne un résultat différent de celui obtenu avec les propositions de Jean-Marcel ou MJ13 ?
Si oui, merci d'avance.​

ROGER2327
#4793

Lundi 23 Sable 138 (Sainte Viole, vierge et martyre, SQ)
3 Nivôse An CCXIX
2010-W51-4T14:35:09Z

 

[Fo_rum]

Re : Comment trouver la dernière cellule ? Pièges avec les formules

Re,

sauf erreur de ma part (gestion de la définition du nom pour ROGER2327), exécuter les macros dans chacune des feuilles.

 

[ROGER2327]

Re : Comment trouver la dernière cellule ? Pièges avec les formules

Re...

D'accord !
J'avais cru lire

(…) Or je veux simplement m'arrêter sur la dernière cellule "écrite".

C'est pourquoi je n'avais pas fait de test pour voir la réaction aux différent types de données. D'où mon étonnement.
Je joins un classeur d'essai.

Une remarque : je n'ai certainement pas fait la connerie d'envoyer bille en tête un Range("A1").Select dans un module de feuille sans avoir pris la précaution de le faire précéder de l'activation de la feuille concernée…
​

ROGER2327
#4794

Lundi 23 Sable 138 (Sainte Viole, vierge et martyre, SQ)
3 Nivôse An CCXIX
2010-W51-4T15:53:52Z

 

[JNP]

Re : Comment trouver la dernière cellule ? Pièges avec les formules

Re ,
C'est vrai que finalement, le gros du problème se situe dans la définition du demandeur ...
Personnellement, j'avais compris "la dernière formule renvoyant un résultat autre que rien" ...
Bonne suite, on aura peut-être une réponse un jour

 

[Fo_rum]

Re : Comment trouver la dernière cellule ? Pièges avec les formules

Re,

Quand la macro est écrite dans le module de "Feuil1", est-il utile d'ajouter ?

  With Sheets("Feuil1")
     .
  end With

A noter que la macro suivante (réactualisée pour une rapidité accrue) donne la même réponse que celle de ROGER23277 dans son contexte.

Private Sub Fo_rum()
  Dim T(), n As Long, Li As Long
  T = Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
  For n = 1 To UBound(T())
    If Left(T(n, 1), 1) <> "=" And T(n, 1) <> "" Then Li = n
  Next
  MsgBox Li
End Sub

Maintenant, philest a bien précisé qu'il testerait et retiendrait ce qui lui conviendrait le mieux.
Je me suis fait plaisir à découvrir les solutions proposées et qui serviront sans doute à d'autres.

 

[Oranger]

Re : Comment trouver la dernière cellule ? Pièges avec les formules

La fonction suivante renvoie l'adresse de la dernière cellule non vide d'une plage calculée grâce à une formule:

Function dernière_ligne(feuille As String, cellule As String)
colonne = Sheets(feuille).Range(cellule).Column
For i = 1 To 65535
If Sheets(feuille).Cells(i, colonne) = "" Then GoTo fin
If Sheets(feuille).Cells(i, colonne) <> "" Then
nb = nb + 1
End If
Next i
fin:
dernière_ligne = Left(cellule, 1) & nb
End Function

Tu utilise la fonction de la façon suivante: a = dernière_ligne("Feuil1", "E1") ici "E1" désigne la première cellule de la plage où il y a une formule

Oranger

 

[MJ13]

Re : Comment trouver la dernière cellule ? Pièges avec les formules

Bonjour à tous

cellule contenant du texte pour ensuite me positionner sur la dernière cellule vide. Or les cellules vides suivantes contiennent des formules du style RechercheV et le code VBA que j'ai écrit plus haut pour rechercher la dernière cellule contenant du texte, va prendre en compte toutes les cellules vierges qui contiennent chacune une formule. Or je veux simplement m'arrêter sur la dernière cellule "écrite".

Déjà quand j'ai lu ça, cela ne me parraissait pas très clair (comme souvent sur XLD) avec "des cellules vierges qui contiennent chacune une formule" . J'avais failli demandé un fichier pour bien comprendre.

Ensuite le débat fut intéressant car ainsi, on a plusieurs solutions, plus ou moins rapide en fonction du code et de ce qu'on cherche.

En bref, un must sur XLD .

 

[MJ13]

Re : Comment trouver la dernière cellule ? Pièges avec les formules

Re à tous

Sinon, je vous ai concocté une petite macro pour trouver la dernière valeur ou texte de la colonne A .

Sub Trouve_Last_Val_Text2() 
'MJ
t1 = Timer
Dim test(65536) As Variant
For i = Cells(65536, 1).End(xlUp).Row To 1 Step -1
test(i) = Cells(i, 1).Formula
If Left(test(i), 1) <> "=" And test(i) <> "" Then Exit For
Next
t2 = Timer - t1
MsgBox t2 & "secondes"
MsgBox i
End Sub

 

[olivowitch]

Re : Comment trouver la dernière cellule ? Pièges avec les formules

Bonjour,

j'ai un problème très simple surement à résoudre que je ne comprend pas.
Je suis sous excel 2007 et de niveau débutant.

Je souhaite faire un copier-coller de la dernière cellule (avec contenu) d'une ligne dans une autre cellule.
Cette dernière cellule pleine est le résultat d'une formule (formule qui permet de séparer tout les éléments d'une même cellule)

Voici ce que j'ai fait :

Sheets("nom_feuille1").Cells(cellule arrivée) = Sheets("nom_feuille1").Cells(2, 256).End(xlLeft).Value

Erreur 1004 : erreur définie par l'application ou par l'objet.

Merci en espérant ne pas être dans un mauvais fil de discussion.

 

[Pierrot93]

Re : Comment trouver la dernière cellule ? Pièges avec les formules

Bonjour,

manque peut être une virgule ici, si 2 variables bien sur.. :

Sheets("nom_feuille1").Cells(cellule , arrivée)

il est vrai que tu aurais du ouvrir un nouveau fil....

bon après midi
@+

 

[olivowitch]

Re : Comment trouver la dernière cellule ? Pièges avec les formules

Merci je vais ouvrir un nouveau fil de discussion.

 

[CBernardT]

Re : Comment trouver la dernière cellule ? Pièges avec les formules

Bonjour à tous,

Essayer avec cette syntaxe :

Sheets("NomFeuilleCible").Range("Cellule_Cible") = Sheets("NomFeuilleSource").Cells(2, 256).End(xlToLeft).Value

 

[olivowitch]

Re : Comment trouver la dernière cellule ? Pièges avec les formules

Salut et merci CBernardT

Avec ta syntaxe, il n'y a plus de message d'erreur mais le contenu de la dernière cellule de la ligne ne se copie pas dans la cellule cible.
J'ai ouvert un file de discussion Ici :

https://www.excel-downloads.com/threads/trouver-la-derniere-cellule-pleine-dune-ligne.155445/

Merci