Discussion :

[JCBGX]

Bonjour,

Je souhaite identifier la derrière cellule d’une colonne en Excel VBA
L’instruction du type

Code :

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

DernLigne = Range("A65536").End(xlUp).Row

ne convient pas car

si la liste comporte (par filtre ou autre) des lignes masquées. L’instruction ci-dessus ne tient pas compte du contenu des cellules masquées (c’est la dernière cellule non masquée qui est identifiée)

Je cherche une solution « intégrée » du type de l’instruction ci-dessus autre que d’aller explorer une à une les lignes de la colonne

Une question du même type a été posée mais en supposant la ou les dernières lignes contiguës masquées avec une solution proposées par boucle pas assez rapide

Quelqu’un a-t-il une idée ?

[bpascal123]

Bonjour,

Le script ci-dessus prend en compte les propriétés "filtres" et lignes masquées de la feuille 1.

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
21
22
Sub derLig()
 
Dim ws As Worksheet
Dim derLig As Long
 
Set ws = ThisWorkbook.Worksheets(1)
 
With ws
 
  'désactive le filtre
  If .AutoFilterMode Then temp.AutoFilterMode = False
 
  'demasque les lignes
  .Columns("A:A").EntireRow.Hidden = False
 
  derLig = .Cells(.Rows.Count, 2).End(xlUp).Row
 
End With
 
Debug.Print derLig
 
End Sub

Ce qui peut être gênant est si vous ne voulez pas changer l'état de la feuille avec les différents filtres et lignes masquées. Dans ce cas, une petite astuce serait de faire une copie de la feuille dans un classeur distinct temporaire, d'appliquer la macro ci-dessus et de supprimer le classeur temporaire. Comme ça, vous garder la valeur de la dernière cellule sans rien changer. C'est un peu plus long en terme d'exécution mais c'est ce qui me semble le plus simple.

Autres possibilités sans macro, utiliser un tcd et récupérer le nombre de ligne en décomptant le nombre de valeurs dans la colonne valeur ou bien peut-être que si vous convertissez vos données en tableaux, vous pouvez en récupérer la dernière ligne avec une formule.

Cordialement

Pascal

[eriiic]

Bonjour,

en te mettant sur une colonne complète, A par exemple :

Code :

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

derlig = [A1].End(xlDown).Row

eric

PS : si 0 ligne filtrée tu obtiens la dernière ligne de la feuille, à tester

Et si tu as besoin des lignes filtrées pour boucler dessus :

Code :

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

1
2
3
4
5
6
7
8
Dim pl As Range
Set pl = [_FilterDataBase].SpecialCells(xlCellTypeVisible)
' dernière ligne devient:
If pl.Areas.Count > 1 Then
  derlig = pl.End(xlDown).Row
Else
  ' 0 lignes
End If

[bpascal123]

Bonjour,

Voici une premiere alternative

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
21
22
23
Sub derLig()
 
Dim ws As Worksheet
Dim v() As String
Dim i As Long
 
Set ws = ThisWorkbook.Worksheets(1)
 
With ws
 
i = .Rows.Count
 
While .Cells(i, 2).Value Like vbNullString
 
  i = i - 1
 
Wend
 
Debug.Print i
 
End With
 
End Sub

une seconde alternative

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
21
22
23
24
25
26
27
28
29
Sub derLig2()
 
Dim ws As Worksheet
Dim v() As String
Dim i As Long, j As Long
 
Set ws = ThisWorkbook.Worksheets(1)
 
With ws
 
ReDim v(0): j = 0
 
For i = 1 To .Rows.Count
 
  If .Cells(i, 2) <> vbNullString Then
 
    ReDim Preserve v(j)
    v(j) = 1
    j = j + 1
 
  End If
 
Next i
 
Debug.Print UBound(v, 1) + 1 'base 0
 
End With
 
End Sub

Le temps d'exécution entre les 2 peut varier.

Cordialement

Pascal

[JCBGX]

Bonjour,

Merci à bpascal123 et eriiic pour vos réponses

Etant contraint par la nécessité de laisser les filtres et/ou les lignes masquées, il ne me reste que la possibilité d'explorer la liste ligne ligne, comme vous le proposez

Cordialement

[eriiic]

????
Pas besoin de boucler. Tu as essayé ma proposition au moins ?

[bpascal123]

Bonjour,

en te mettant sur une colonne complète, A par exemple :

Code :

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

derlig = [A1].End(xlDown).Row

eric

PS : si 0 ligne filtrée tu obtiens la dernière ligne de la feuille, à tester

Bonjour Eriiic

D'après le script ci-dessus, il vaut mieux utiliser xlUp plutot que xlDown. Dans ce cas, s'il y a une ligne vide, derlig s'arrête à cette ligne même s'il y a des lignes pas vide plus loin.

Après pour le second script, je n'ai as réussi à le tester (je suis curieux de voir si c'est plus rapide que de boucler sur la totalité des lignes du classeur). Il faudrait le script en entier car on ne peut pas voir où est instancié l'objet pl range.

Cordialemlent

Pascal

[eriiic]

Bonjour,

Dans ce cas, s'il y a une ligne vide, derlig s'arrête à cette ligne

J'ai bien précisé : se mettre sur une colonne complètement remplie.
S'il n'y en a pas la 2nde proposition fonctionne.

Ci-joint classeur avec les 2 exemples.
eric

[JCBGX]

Eric,

J'ai effectivement réalisé le test, d'autant plus que je recherche une solution "synthétique" depuis un moment
Sur l'exemple que tu donnes, en fait je voudrais voir apparaître 52 (la dernière ligne renseignée) et non pas 47 la dernière ligne vide

Cordialement
JC

[FauconV]

Pour gagner du temps d'execution, il est preferable de travailler sur des tableaux (ayant le contenu des cellules à inspecter) plutot que directement sur les cellules.

J'ai également été confronté à ce problème de cellule masquées/repliées via un groupe/filtre.
Le code que je propose copie le contenu de la colonne a inspecter dans un tableau. Ensuite boucle sur ce tableau et analyse en partant de la fin du tableau. On arrete dès qu'on trouve qu'une case est non vide. Attention vbNullString ne permet pas de gérer des cellules ayant une formule qui renvoient "".
J'ai mis ce code d'analyse dans une fonction. La sub est pour tester le code de la fonction.

Le code de la sub:

Code :

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

1
2
3
4
5
6
7
8
9
Sub Test_DerLigne_function()
Dim ws As Worksheet
Dim Resultat As Long
 
    Set ws = ThisWorkbook.ActiveSheet
 
    Resultat = DerLigne_function(ws, "B")
    MsgBox (Resultat)
End Sub

Le code de la fonction:

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
21
22
Function DerLigne_function(ws As Worksheet, Optional ByVal Colonne As String = "A") As Long
Dim TabInspection() As Variant
Dim i As Long, j As Long
Dim Resultat As Long
 
    'sauvegarder dans un tableau, la colonne à inspecter (permet des temps execution plus court)
    TabInspection = ws.Range(Colonne & "1:" & Colonne & ws.Rows.Count)
 
    'on commence l'inspection par le bas de la colonne. Si on trouve une valeur, on arrete l'inspection
    For i = LBound(TabInspection, 1) To UBound(TabInspection, 1)
        j = UBound(TabInspection, 1) - i + 1
 
        'vbEmpty permet de gerer les cellules avec une formule qui renvoie "" contrairement à vbNullString
        If TabInspection(j, 1) <> vbEmpty Then
            'on a trouvé une valeur => on arrete la boucle
            Resultat = j
            Exit For
        End If
    Next i
 
DerLigne_function = Resultat
End Function

Le temps d'execution est relativement court.

Cordialement,

Bonjour,

une seconde alternative

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
21
22
23
24
25
26
27
28
29
Sub derLig2()
 
Dim ws As Worksheet
Dim v() As String
Dim i As Long, j As Long
 
Set ws = ThisWorkbook.Worksheets(1)
 
With ws
 
ReDim v(0): j = 0
 
For i = 1 To .Rows.Count
 
  If .Cells(i, 2) <> vbNullString Then
 
    ReDim Preserve v(j)
    v(j) = 1
    j = j + 1
 
  End If
 
Next i
 
Debug.Print UBound(v, 1) + 1 'base 0
 
End With
 
End Sub

Le temps d'exécution entre les 2 peut varier.

Cordialement

Pascal

La 2e alternative ne fonctionne que s'il y a pas de ligne vide au milieu des données.

Cordialement,

Petite précision, dans le code que j'ai donné, la sub de test travaille sur la colonne B. A adapter selon le besoin.

Cordialement