Dernière cellule non vide dans une colonne variable

**Mickael44300** · 29/08/2015, 11h52

Bonjour,

Je rencontre une difficulté sur VBA.
Je dois aller chercher la dernière cellule non vide dans une colonne. La difficulté est que je suis dans une boucle (For / Next) et que le numéro de la colonne change.
Sauriez-vous comment faire?

Merci

**Thautheme** · 29/08/2015, 13h15

Bonjour Mickael, bonjour le forum,

Si l'on considère la variable COL comme le numéro de colonne dans la boucle et DL la dernière ligne de la colonne ça devrait le faire avec :

Code :

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

DL = Cells(Application.Rows.Count, COL).End(xlUp).Row

**Mickael44300** · 29/08/2015, 13h24

Merci je vais essayer ça

Comme il s'agit d'un projet assez compliqué (pour moi) dans lequel s'inscrit cette problématique, je risque de revenir pour d'autres problèmes ^^

**Quentin77170** · 29/08/2015, 19h46

En fait, juste pour préciser, la fonction Range.End(xlDirection) trouve la dernière cellule non vide si elle est lancée depuis une range non vide, si elle est lancée depuis une zone vide, elle trouvera la PREMIERE cellule non vide...

En gros :

	A	B	C	D	E
1	XXX	XXX		XXX
2	XXX
3
4	XXX
5

Si tu veux la dernière cellule en colonne A, alors tu es tenté d'utiliser Range("A1").End(xlDown)... Ce qui, dans ce cas, fonctionnera parfaitement. Seulement, si tu veux être certain d'utiliser la dernière cellule de la colonne, part d'en bas, donc de la

Code :

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

1
2
3
4
5
Range("A" & Rows.Count).End(xlUp) 
'que tu peux également écrire 
'Cells(Rows.Count, "A").End(xlUp) 
'voire 
Cells(Rows.Count, 1).End(xlUp)

Rows.Count indiquera le numéro de la dernière ligne (égal au nombre de lignes...), passer par cette propriété est important car suivant la version du workbook, tu n'auras pas le même nombre de lignes, donc Range("A1048576").End(xlUp) pourrait planter chez toi.

Pourquoi est-il préférable d'utiliser la dernière cellule du bas et remonter ? Pour la simple et bonne raison que s'il y a ne serait-ce qu'UN trou dans ta colonne, la fonction End s'arrêtera sur la dernière cellule avant ce trou. Par exemple, Range("A1").End(xlToRight) te donnera la Range("B1") et non la "D1" que tu vises ! Donc tu pars de la droite avec :

Code :

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

Cells(1,Columns.Count).End(xlToLeft)

Ensuite, pour récupérer le numéro de ligne de la cellule désignée, tu lis la propriété .Row comme indiqué par Thautheme plus haut :

Code :

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

DerLigne = Cells(Rows.Count,[TaColonne]).End(xlUp).Row

**kiki29** · 30/08/2015, 00h05

Salut, toujours utile de lire ceci

**Mickael44300** · 30/08/2015, 22h05

Merci de vos réponses !

Cependant, je n'arrive pas à résoudre complètement cela ...
Voici mon code :

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
30
Sub Bilan_MP()
 
Dim A As Integer 'a est le numéro de feuille
Dim I As Long 'i est le numéro de colonne dans les tableaux de maintenance
Dim J As Long 'j est la ligne dans la feuille "Bilan"
Dim NEWDATE As Date 'Prochaine échéance de maintenance
Dim DATEDEBUT As Date 'Début de la période observée
Dim DATEFIN As Date 'Fin de la période observée
Dim COL As Long 'Numéro de colonne dans la boucle
Dim DL As Long 'Numéro de ligne de la dernière cellule non vide
Dim DERNIEREDATE As Date 'Dernière date de la colonne observée
 
Cells(6, 2).Select
For A = 3 To Sheets.Count
For I = 3 To Columns.Count
 
DL = Cells(Rows.Count, I).End(xlUp).Row
DERNIEREDATE = Cells(DL, I)
 
NEWDATE = DateAdd("m", Cells(13, I), DERNIEREDATE)
DateDébut = Sheets("Bilan").Range("C1")
DATEFIN = Sheets("Bilan").Range("E1")
If Not IsEmpty(Sheets(A).Cells(12, I).Value) And CDate(NEWDATE) > CDate(DATEFIN) And CDate(NEWDATE) < CDate(DateDébut) Then
ActiveCell.Value = Sheets(A).Cells(12, I).Value
ActiveCell.Offset(1, 0).Select
End If
Next I
Next A
 
End Sub

Quand je lance la macro, j'ai une incompatibilité de type pour : DERNIEREDATE = Cells(DL, I)

Ce que je tente de faire, est ceci:

Je cherche dans ma colonne la dernière date renseignée.
Si cette date est comprise dans entre DATEDEBUT et DATEFIN alors elle doit apparaître dans ma feuille "BILAN".

**Thautheme** · 30/08/2015, 23h21

Bonsoir le fil, bonsoir le forum,

Tu boucles sur toutes les colonnes et je doute que toutes soient éditées. Si, dans ta boucle, la dernière cellule d'une colonne n'est pas une date ça va planter !...
Essaie comme ça :

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
30
31
Sub Bilan_MP()
 
Dim A As Integer 'a est le numéro de feuille
Dim DC As Integer 'déclare la variable DC (Dernière Colonne)
Dim I As Long 'i est le numéro de colonne dans les tableaux de maintenance
Dim J As Long 'j est la ligne dans la feuille "Bilan"
Dim NEWDATE As Date 'Prochaine échéance de maintenance
Dim DATEDEBUT As Date 'Début de la période observée
Dim DATEFIN As Date 'Fin de la période observée
Dim COL As Integer 'Numéro de colonne dans la boucle
Dim DL As Long 'Numéro de ligne de la dernière cellule non vide
Dim DERNIEREDATE As Date 'Dernière date de la colonne observée
 
Cells(6, 2).Select
For A = 3 To Sheets.Count
    DC = Sheets(A).UsedRange.SpecialCells(xlCellTypeLastCell).Column
    For I = 3 To DC
        DL = Cells(Rows.Count, I).End(xlUp).Row
        If IsDate(Cells(DL, I)) = True Then 'condition : si la dernière cellule de la colonne de la boucle est une date
            DERNIEREDATE = CDate(Cells(DL, I))
            NEWDATE = DateAdd("m", Cells(13, I), DERNIEREDATE)
            DateDébut = CDate(Sheets("Bilan").Range("C1"))
            DATEFIN = CDate(Sheets("Bilan").Range("E1"))
            If Not IsEmpty(Sheets(A).Cells(12, I).Value) And CDate(NEWDATE) > CDate(DATEFIN) And CDate(NEWDATE) < CDate(DateDébut) Then
                ActiveCell.Value = Sheets(A).Cells(12, I).Value
                ActiveCell.Offset(1, 0).Select
            End If
        End If 'fin de la condition
    Next I
Next A
End Sub

**Mickael44300** · 30/08/2015, 23h35

Merci de ton aide Thautheme

Je ne comprends pas ce que signifie Sheets(A).UsedRange.SpecialCells(xlCellTypeLastCell).Column.
Lorsque je lance la macro, je n'ai plus de message d'erreur mais il ne se passe rien ...

Test macro maintenance (Enregistré automatiquement).xlsm

Je mets mon fichier, peut-être comprendras-tu mieux mon erreur ainsi

Mickaël

**Thautheme** · 31/08/2015, 00h52

Re,

• D'abord, ton code commence par : Cells(6, 2).Select pour finir par par Activecell.Value = Sheets(A).Cells(12, I).Value mais tu ne précises pas de quel onglet il s'agit!?...

• Ensuite, quand on fait tourner la macro pas à pas, jamais les conditions : If Not IsEmpty(Sheets(A).Cells(12, I).Value) And CDate(NEWDATE) > CDate(DATEFIN) And CDate(NEWDATE) < CDate(DateDébut) ne sont vérifiées. Il est donc normal que rien ne se passe...

Sinon, plutôt que de boucler sur toutes les colonnes on va récupérer la dernière utilisée (éditée) de l'onglet A avec :

Code :

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

Sheets(A).UsedRange.SpecialCells(xlCellTypeLastCell).Column

**Theze** · 31/08/2015, 16h45

Bonjour,

Juste deux petite précisions.
Il est préférable d'utiliser la collection "Worksheets" plutôt que la collection "Sheets" surtout dans une boucle car si il y a une ou plusieurs feuilles graphique ça plante !
Il faut aussi faire attention avec "UsedRange" car on peut avoir des surprises quant à la plage retournée ! Pour le tester, il suffit de remplir quelques cellules et plus loin et plus bas que la plage, changer juste la fonte d'une cellule vide ou même la couleur de la fonte sans entrée aucune valeur et ensuite tester cette proc :

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
 
Sub Test()
 
    'la plage défini par UsedRange
    MsgBox "Plage : " & ActiveSheet.UsedRange.Address(0, 0)
 
    'la dernière cellule utilisée (attention, qui peut être vide !)
    MsgBox "Cellule en bas à droite : " & ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Address(0, 0)
 
    'idem pour la colonne
    MsgBox "Colonne la plus à droite colonne : " & ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column
 
    'et de même pour la ligne
    MsgBox "Ligne la plus basse : " & ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
 
End Sub

Si on fait une recherche pour définir la plage englobant les cellules les plus à droite et les cellules les plus basses qui contiennent une valeur, il est préférable d'utiliser ce type de 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
 
Sub Test()
 
    MsgBox Plage(ActiveSheet).Address(0, 0)
 
End Sub
 
Function Plage(Fe As Worksheet) As Range
 
    With Fe
 
        Set Plage = .Range(.Cells(1, 1), .Cells( _
                    .Cells.Find("*", .Cells(1, 1), -4123, , 1, 2).Row, _
                    .Cells.Find("*", .Cells(1, 1), -4123, , 2, 2).Column))
 
    End With
 
End Function

Attention tout de même, cette fonction définie la cellule de base de la plage en A1 mais il est possible d'adapter pour trouver une plage en milieu de feuille.

Hervé.

**Mickael44300** · 01/09/2015, 20h59

Merci de votre aide. Tout fonctionne

**RyuAutodidacte** · 10/10/2017, 21h50

Bonsoir,

Theze : Sympa ta fonction

Dernière cellule non vide dans une colonne variable

Macros et VBA Excel

Discussions similaires

Partager

Partager