Discussion :

[VC1992]

Bonsoir,

J'ai ouvert cette discussion car je rencontre un problème dans la récupération de la valeur de l'avant-dernière cellule non vide d'une colonne. Cette valeur peut être aussi bien au format nombre que texte.

Pourriez-vous m'aider svp?

Je vous remercie par avance.

Cordialement

[marot_r]

Bonjour.

En supposant que tu n'as pas de blanc en cours de route, quelque chose d'assez direct comme

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
dim cell as range
dim result as variant: result=null
 
for each cell in thisworkbook.worksheets("TaFeuille").columns("A").cells 'Parcourt toutes les cellules de la colonne
 
   if cell.value="" then
      'Cette cellule est vide
 
      if cell.row-2 > 0 then
         'Il y a un moins 2 lignes avant la cellule vide
         result=cell.offset(-2,0).value 'Lit l'avant dernière cellule de la colonne
      end if
 
      exit for
 
   end if
 
next cell
 
debug.print result

[Pierre Dumas]

Bonsoir

Voici une solution par formule. Là aussi, à condition qu'il n'y ait pas de cellules vides.
=INDEX(A1:A20;NBVAL(A1:A20)-1)

En espérant que cela aide

Bonne nuit

Pierre Dumas

[Tête de chat]

Bonjour,

S'il y a des cellules vides, et par formule

... d'abord avec du texte

la dernière

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=EQUIV("";A:A;-1)

et donc l'avant dernière

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=EQUIV("";DECALER(A1;0;0;EQUIV("";A:A;-1)-1;1);-1)

le decaler permet de limiter la colonne à la portion au dessus de la dernière ligne déterminée par la formule précédente.

... puis avec du numérique ; s'il y a des nombres ils ne sont pas vus par l'equiv, qu'à cela ne tienne on les transforme en string

la dernière

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=EQUIV("";SI(A:A="";A:A;A:A&"");-1)

le si sert à ne pas remplir les cellules vides avec ""

et donc l'avant-dernière

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=EQUIV("";SI(DECALER(A1;0;0;EQUIV("";SI(A:A="";A:A;A:A&"");-1)-1;1)="";DECALER(A1;0;0;EQUIV("";SI(A:A="";A:A;A:A&"");-1)-1;1);DECALER(A1;0;0;EQUIV("";SI(A:A="";A:A;A:A&"");-1)-1;1)&"");-1)

avec le decaler idem mais répété 3 fois en remplacement des A:A dans la formule précédente...

À chaque fois on récupère le n° de ligne, si on veut la valeur reste à faire un index, et sans doute un sierreur des fois que le tableau soit vide ou avec une seule valeur.
Des fois un peu de VBA est plus simple qu'une formule !

[Tête de chat]

Re,

et donc en VBA avec des vides :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
Option Explicit
Function AvantDerniereNonVide(MaColonne As Range) As Long
' renvoie la ligne de l'avant-dernière cellule non vide de la première colonne de la range ; 0 si aucune ou une valeur non vide
Dim I As Long
I = MaColonne.Cells(MaColonne.Rows.Count, 1).End(xlUp).Row - 1 'on commence au dessus de la dernière..
Do While I > 0
    If MaColonne.Cells(I, 1).Value <> "" Then Exit Do
    I = I - 1
Loop
AvantDerniereNonVide = I
End Function

ou

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Option Explicit
Function DerniereNonVide(MaColonne As Range, Optional ByVal Rang = 1) As Long
' renvoie la ligne de Rng-ième cellule non vide à partir de la fin de la première colonne de la range ; 0 si pas assez de valeurs non vide
Dim I As Long
I = MaColonne.Cells(MaColonne.Rows.Count, 1).End(xlUp).Row
'on commence au dessus de la dernière..
Do While I > 0
    If MaColonne.Cells(I, 1).Value <> "" Then
        Rang = Rang - 1
        If Rang = 0 Then Exit Do
    End If
    I = I - 1
Loop
DerniereNonVide = I
End Function

à appeler donc =DerniereNonVide(A:A;2) pour l'avant-dernière

Comme précédemment renvoie une ligne donc à adapter si on veut la valeur.

[Pierre Dumas]

Bonjour

La question m'a taraudé un peu, et j'ai trouvé ceci sur le Net :
=INDEX(A1:A20;GRANDE.VALEUR(SI(A1:A20<>"";LIGNE(A1:A20));2))

C'est à valider par Ctrl+Shift+Entrée pour les versions autre que 365 et 2021.
Elle fonctionne avec du texte et des nombres.
Elle fonctionne avec des cellules vides.

C'est un peu plus long que ma première proposition, mais moins restrictif

En espérant que cela aide

Bonne journée

Pierre Dumas

[Raccourcix]

Bonjour

une possibilité avec une version récente d'Excel

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
=CHOISIRLIGNES(FILTRE(plage;NON(ESTVIDE(plage)));-2)
ou
=CHOISIRLIGNES(FILTRE(plage;plage<>"");-2)

FILTRE pour filtrer la plage sur les cellules non vides
CHOISIRLIGNES(...;-2) pour récupérer l'avant dernière.

Stéphane

[Niagala]

Bonjour à tous
Une autre proposition en VBA qui prend tout en compte

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
Private Sub CommandButton1_Click()
Dim DL As Long 'Dernière Ligne non vide de la colonne -1
DL = Feuil1.Range("A" & Rows.Count).End(xlUp).Row - 1
Range("A" & DL).Select
If Range("A" & DL) <> "" Then
TextBox2 = Range("A" & DL).Value
Else
Do Until ActiveCell <> ""
    ActiveCell.Offset(-1, 0).Select 'On remonte d'une ligne 
Loop
TextBox2 = ActiveCell.Value
End If
End Sub

[VC1992]

Merci beaucoup pour vos réponses!
La formule =INDEX(A1:A20;NBVAL(A1:A20)-1) a parfaitement fait l'affaire étant donné que je n'avais pas de cellules vides dans la colonne.

Vous m'avez été d'une grande aide, c'est top! Merci!

Bonne soirée

[Niagala]

La formule =..................... a parfaitement fait l'affaire étant donné que je n'avais pas de cellules vides dans la colonne.

On était obligé de prévoir les cellules vides parce que vous l'avez mentionné dans le titre.

N'oubliez pas de mettre « RESOLUE»
Merci.

[VC1992]

Oui désolé, les cellules vides étaient en fin de colonne. Je me suis mal exprimé dans mon sujet.
Mais merci beaucoup!

Sujet résolu