Contrôler qu'une ligne est vide, à partir de la colonne C et jusqu'à la fin

Bonjour à Toutes, à Tous, et à ceux qui toussent !

Plus sérieusement, j'ai une macro qui doit supprimer une ligne d'une feuille Excel, sous condition.

La condition est que cette ligne ait été préalablement vidée de toutes ses données, par précaution. Sauf les deux cellules A et B, car elles servent précisément à sélectionner la ligne à supprimer. Je précise que la feuille peut compter plusieurs milliers de colonnes (une par jour pendant des années).

L'idée serait donc de pouvoir vérifier si la plage de [C à la fin de ligne] est vide (depuis la colonne C jusqu'à la fin). Si elle est vraiment vide, la macro supprime la ligne, sinon MsgBox "Alerte" et Exit Sub.

J'ai tenté beaucoup de choses avec Empty, avec CountA, ... Sans succès . Snif !

Merci de vos lumières.

Voir la description du problème avec un exemple factice en PJ (Pièce Jointe, non pas Police Judiciaire. Quoi que parfois, les recherches en VBA confinent à des enquêtes criminelles. LOL ! ).

J'en appelle donc aux fins limiers que vous êtes, en vous remerciant par avance).

Bonjour,

Voici un début de solution
Il faut double cliquer sur la feuille pour obtenir la réponse
Mais je ne suis pas totalement satisfat car je n'ai testé que les cellules de la colonne C pour voir si elles étaient vides ou non, mais rien ne me dit que les cellules D,E,F etc sont ou ne sont pas vides
Ma proposition est donc à approfondir

Bonjour à tous,

Bonne idée jacky mais on peut faire plus simple (et rapide) :

Il suffit d'utiliser la fonction "End(xlToRight)" qui est l'équivalent de CTRL+[→] sur le clavier. On regarde si la colonne est la dernière de la feuille : si oui ligne vide, sinon ligne non vide.

Je vous laisse implémenter la fonction selon le besoin/sinon préciser car je n'ai pas bien compris.

On lui donne le numéro de ligne et elle renvoie True/VRAI si la ligne est vide dans la feuille active.

Public Function EstVide(ligneI As Long) As Boolean
  With ActiveSheet
    EstVide = (.Cells(ligneI, 2).End(xlToRight).Column = .Columns.Count)
  End With
End Function

bonjour le fil,

@saboh12617, avec "activesheet", c'est assez dangeureux, utilisez une cellule comme variable.

@jacky, normallement avec ce genre de questions, il faut inverser le boucle parce que le question suivante sera de supprimer cette ligne, donc

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
     For i = Me.Cells(Rows.Count, 1).End(xlUp).Row To 9 Step -1
          If WorksheetFunction.CountA(Me.Cells(i, 3).Resize(, Columns.Count - 2)) = 0 Then
               MsgBox ("la ligne " & i & " est vide")
               Me.Rows(i).Delete
          End If
     Next i
End Sub

Salut @BsAlv,

Oui, justement je l'ai mis explicitement pour rendre le code adaptable à la feuille réelle en changeant simplement le bloc With. C'était plus pour donner une structure/mode de résolution.

Bonjour Bart, bonjour Saboh

Evidemment vous êtes toujours là pour améliorer les propositions
C'est formidable et une fois de plus vous me permettez de réfléchir un peu plus loin que le bout de mon nez
Au grand plaisir de vous retrouver

Merci Saboh 12617

Je n'ai pas encore testé. Dans la soirée, j'espère !

Cette formule me parait élégante. Car je n'ai pas besoin de détecter quelle ligne est à tester. Donc pas besoin de boucle. En fait, j'ai besoin d'un contrôle avant de supprimer la ligne que j'ai choisie moi-même (ligne I dans ton code, c'est parfait).

Mais que représente le 2 dans ta formulation ? Si c'est la colonne, la ligne doit être vide à partir de la colonne C, donc la troisième...

Cordialement.

Bonjour,

Merci pour votre retour, ci-joint un fichier plus complet :

Clic sur le bouton "tout scanner" : vous étudie la plage à partir de la cellule A9 de la feuille active, et vous informe des lignes vides/non vides. Dans le code vous pouvez enlever le commentaire pour supprimer les lignes vides.

Pour reprendre l'exemple de mes p'tits potes à la compote, si double clic sur une cellule des colonnes A:B, cela check la ligne courante, et le cas échéant vous informe si la ligne est non vide. On peut aussi enlever le commentaire pour supprimer les lignes vides.

Pour répondre à votre question, on part de la cellule en colonne B car on effectue l'équivalent de CTRL+[→] au clavier. On doit partir un peu décalé car si on part de C, on oublie toutes les colonnes ou la cellule C est pleine.

Pour comprendre, sur le fichier joint, ajoutez une valeur en C13, et modifiez dans le code 2 en 3. Vous verrez qu'il dira "tout va bien, ligne vide" alors qu'il y a une valeur. Il faut se décaler, comme au clavier.

Ci-joint le fichier. Et ci-après le code.

Public Sub NettoyerTout()
  Dim sht As Worksheet
  Set sht = ActiveSheet

  Dim ligneIni As Long, ligneF As Long, i As Long
  ligneIni = 9: ligneF = sht.Cells(ligneIni, 1).End(xlDown).Row

  Dim lignesV As Long, lignesNV As Long

  For i = ligneF To ligneIni Step -1
    If EstVide(i, sht) Then
      lignesV = lignesV + 1
      ' pour supprimer la ligne vide, enlever le commentaire
      ' sht.Rows(i).Delete
    Else
      lignesNV = lignesNV + 1
    End If
  Next i

  MsgBox "Sur les lignes " & ligneIni & " à " & ligneF & vbCrLf & _
         lignesV & " lignes vides trouvées" & vbCrLf & _
         lignesNV & " lignes non vides trouvées", _
         vbInformation, _
         "RECAP"
End Sub

Public Sub NettoyerLigne(ligneI As Long, sht As Worksheet)
  If Not EstVide(ligneI, sht) Then
    MsgBox "Alerte : Ligne " & ligneI & " non vide !", vbExclamation, "ALERTE"
  Else
    ' pour supprimer la ligne vide, enlever le commentaire
    ' sht.Rows(ligneI).Delete
  End If
End Sub

Public Function EstVide(ligneI As Long, sht As Worksheet) As Boolean
  With sht
    EstVide = (.Cells(ligneI, 2).End(xlToRight).Column = .Columns.Count)
  End With
End Function

' -------------- Dans "Feuil1"
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If Target.Column < 3 Then NettoyerLigne Target.Row, Me
End Sub

@Saboh12617,

imaginez que la ligne est "plein" à partir de la 2eme colonne jusqu'au bout >>>> votre fonction dira "Vide",

Public Function EstVide(Cellule As Range) As Boolean
     With Cellule(1)                         'si ce sont plusieurs cellules, on prend le "TopLeftCell"
             EstVide = (WorksheetFunction.CountA(.Offset(, 3 - .Column).Resize(, Columns.Count - 2)) = 0)
     End With
End Function

cette construction ".End(xl....)" est à traiter avec précaution !!! S'il y a des cellules pleines mais cachées (lignes filtrées ou colonnes cachées), elles seront ignorées et donc considérées comme vide !!!

Et on ne parle pas encore des cellules fusionnées sur plusieurs lignes

@BsAlv, j'avais pensé à la ligne pleine (l'op indique que non à priori), mais pas aux autres cas…

Oui, COUNTA n'est pas sujet à ces erreurs, je voulais simplement faire un truc le plus "rapide", mais ça l'est un peu trop hahaha.

Dans ce cas je reprends ta formule plus exacte, pour remplacer celle que j'ai utilisée, en gardant le reste du code fonctionnel.

Public Function EstVide(ligneI As Long, sht As Worksheet) As Boolean
    EstVide = (WorksheetFunction.CountA(sht.Cells(ligneI, 3).Resize(, Columns.Count - 2)) = 0)
End Function

Merci pour la précision, Goedenavond !

Merci Saboh 12617 pour la réponse complémentaire.

Je vais implémenter cela ce soir. Si ça marche pour moi, je clôturerais le topic.

Bonne soirée

@saboh12617,

Buonasera

Bonjour Le Forum,

Je vous remercie pour toutes vos explications.

Voici ce que j'ai retenu, mis en place et validé dans mon code principal :

Je vous souhaite une bonne soirée.

re,

sans fichier, c'est difficile, mais la ligne "if Activesheet.previous.application.counta(...." me surprend

Vous supprimez vos lignes de la première vers la dernière ou l'inverse ?

Bonsoir à tous ,

Moi aussi je veux être de la danse.

Une méthode assez rapide (me semble-t-il). Pour 10 000 lignes à examiner dont 2 000 à supprimer, la durée d'exécution sur ma bécane est d'environ 0,23 s.

le code dans module1 :

Sub SupprLigne()
Const Feuille = "Feuil1", PremligneExamen = 9, PremColonneExamen = "C"
Dim BorneCol&, derlig&, dercol&, i&, dercolval&, ajout As Boolean, deb#
   deb = Timer
   Application.ScreenUpdating = False
   BorneCol = IIf(TypeName(PremColonneExamen) = "String", Cells(1, PremColonneExamen).Column, PremColonneExamen)
   With Worksheets(Feuille)
      derlig = .UsedRange.Row + .UsedRange.Rows.Count - 1
      dercol = .UsedRange.Column + .UsedRange.Columns.Count - 1
      ReDim indic(1 To derlig, 1 To 1)
      On Error Resume Next
      For i = PremligneExamen To derlig
         dercolval = 0: dercolval = .Rows(i).Find(what:="*", SearchDirection:=xlPrevious).Column
         If dercolval < BorneCol Then indic(i, 1) = CVErr(xlErrNA) Else indic(i, 1) = i
      Next i
      .Columns(dercol + 1).Resize(derlig) = indic: ajout = True
      .Range(.Cells(PremligneExamen, 1), .Cells(derlig, dercol + 1)).Sort _
            key1:=.Cells(PremligneExamen, dercol + 1), order1:=xlAscending, Header:=xlNo
      .Range(.Cells(PremligneExamen, dercol + 1), .Cells(derlig, dercol + 1)).SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
      If ajout Then .Columns(dercol + 1).Delete
   End With
   MsgBox "Méthode rapide : " & Format(Timer - deb, "0.00\ sec.")
End Sub

Salut les as,

pfiouu, 0.23 sec!
Ce n'est plus une bécane, c'est Falcon Heavy!
Sur mon vieux clou, en adaptant ta macro à ma BDD de 25.000 lignes X 9.000 colonnes, ma macro fait jeu égal... en +-75 secondes!!

Tu détailles ta config, stp!

A+

Bonsoir curulis57,

Ah que nenni. Ta valeur est cohérente et du même ordre de grandeur que sur ma bécane. Je suis même un peu plus lent que toi (environ 80 s) avec 25 000 lignes sur 9 000 colonnes.

Quand j'arrive à un fichier de près de 225 millions de cellules et qui pèsent plus de 590 Mo, je prends une pause et m'interroge. Est-ce que Excel est encore le bon outil ? Ne peut-on pas diviser pour mieux régner ? De tels fichiers, je n'y touche pas .

Ma config n'est pas extraordinaire et en performance elle semble à priori être similaire à ta machine.

Ma config qui date de mai 2020 déjà : AMD Ryzen 7 3700X 8-Core Processor - Memory Size 16 GBytes DDR4 - SSD 1To - NVIDIA GeForce RTX 2070. Rien de bien extraordinaire.

Bonsoir Mafraise,

je ne connais pas la correspondance avec ta machine.

i5-3360M CPU @ 2.80GHz 8 Go RAM SSD 220 Go Carte HD

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'
Dim rCells As Range, tTab, lgRow&, lgRow1&, iCol&
'
Cancel = True
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'
iCol = UsedRange.Columns.Count
lgRow = Range("A" & Rows.Count).End(xlUp).Row
tTab = Range("A1:A" & lgRow).Value
For x = 1 To lgRow
    If WorksheetFunction.CountA(Range("C" & x).Resize(1, iCol - 2)) = 0 Then tTab(x, 1) = ""
Next
Range("A1:A" & lgRow).Value = tTab
Range("A1").Resize(lgRow, iCol).Sort key1:=Range("A1"), order1:=xlAscending, Orientation:=xlTopToBottom, Header:=xlNo
lgRow1 = Range("A" & Rows.Count).End(xlUp).Row
Range("B" & lgRow1 + 1).Resize(lgRow - lgRow1, 1).Value = ""
'
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
'
End Sub

C'est le SORT qui prend évidemment tout le temps!

bonjour le fil, une variante de la macro de mafraise = "Plan_B" = la moitié du temps

cad. (mon ordinateur est plus lent que mafraise) 0.44 pour "SupprLigne" et 0.21 pour "Plan_B" (variable)

Sub Plan_B()
     Const Feuille = "Feuil1", PremligneExamen = 9, PremColonneExamen = "C"
     Dim BorneCol&, derlig&, dercol&, i&, dercolval&, ajout As Boolean, deb#
     deb = Timer
     Application.ScreenUpdating = False
     With Worksheets(Feuille)
          derlig = .UsedRange.Row + .UsedRange.Rows.Count - 1
          dercol = .UsedRange.Column + .UsedRange.Columns.Count - 1
                   With .Cells(PremligneExamen, dercol + 1).Resize(derlig - PremligneExamen + 1)
               .FormulaR1C1 = "=IF(IFERROR(AGGREGATE(14,6,COLUMN(RC1:RC[-1])/(RC1:RC[-1]<>""""),1),1)<=3,ROW(),"""")"
               .Value = .Value
               .Offset(, -dercol).Resize(, dercol + 1).Sort .Cells(1, 1), xlAscending, Header:=xlNo
               i = WorksheetFunction.CountBlank(.Offset(0))
               If i > 0 Then .SpecialCells(xlBlanks).EntireRow.Delete
               If i < derlig - PremligneExamen + 1 Then .ClearContents
          End With
     End With
     MsgBox "Méthode rapide : " & Format(Timer - deb, "0.00\ sec.")
End Sub

C'est le SORT qui prend évidemment tout le temps!

C'est ce "sort" qui est si génial, sans cette ligne ....(10 sec chez moi)

Salut BsAlv,

pourrais-tu me faire cette formule pour un "échantillon" débutant en [A1] sur 25000 lignes et 9000 colonnes ?
...comprends rien aux RC!