effacer lignes cachées

[sinedv]

Bonjour,

Pour optimiser et surtout alléger un grand tableau (> 20 000 lignes et 250 colonnes), j'ai écris une macros pour effacer les lignes masquées par l'utilisation d'un filtre automatique.

Sub del_hidden_rows()
    Application.ScreenUpdating = False
    timestart = Timer
    Application.Calculation = xlManual
    curlig = 9
    Do Until (IsEmpty(Cells(curlig, 1)))
        Cells(curlig, 1).Select
        If (Cells(curlig, 1).Rows.Hidden) Then
            Rows(curlig).Delete
        Else
            curlig = curlig + 1
        End If
    Loop
    Calculate
    timestop = Timer - timestart
    msgtxt = "Del hidden rows duration: " & timestop
    Application.ScreenUpdating = True
    Application.Calculation = xlAutomatic
    MsgBox (msgtxt)
End Sub

Cette macro fonctionne très bien ... sauf qu'elle prends plusieurs minutes ...

Avez-vous des idées pour optimiser le temps d'exécution ?

merci d'avance, 🙂

 

[Guest]

Re : effacer lignes cachées

Bonjour,

voici qui devrait améliorer les choses

Sub del_hidden_rows()
    Dim plage As Range
    Dim derlig As Long
    Dim curlig As Long
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    timestart = Timer
    Application.Calculation = xlManual
 
    derlig = Range("A" & Rows.Count).End(xlUp).Row
    For curlig = 9 To derlig
        If Rows(curlig).Hidden = True Then
            If plage Is Nothing Then
                Set plage = Rows(curlig)
            Else
                Set plage = Union(plage, Rows(curlig))
            End If
        End If
    Next
    If Not plage Is Nothing Then plage.EntireRow.Delete
    Calculate
    timestop = Timer - timestart
    msgtxt = "Del hidden rows duration: " & timestop
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.Calculation = xlAutomatic
    MsgBox (msgtxt)
End Sub

A+

 

[sinedv]

Re : effacer lignes cachées

Merci pour cette réponse rapide.

dans mon cas, il s'agit bien d'effacer les lignes cachées ...

je pense que

derlig = Range("A" & Rows.Count).End(xlUp).Row

ne prend en compte que les lignes visibles.

c'est la raison pour laquelle j'étais passé par une boucle do until.

une idée pour compter rapidement le nombre de lignes (visibles et cachées) ... sans faire une boucle qui passe en revue les lignes une à une ?

bonne journée !

 

[Guest]

Re : effacer lignes cachées

re,

tu peux remplacer:

derlig = Range("A" & Rows.Count).End(xlUp).Row

Par

derlig = usedRange.rows.count

sinon,

fais une boucle Do....Loop si tu veux, mais l'idée est de créer une plage de cellules à effacer en un seul coup, ce qui est extrêmement plus rapide.

        If Rows(curlig).Hidden = True Then
            If plage Is Nothing Then
                Set plage = Rows(curlig)
            Else
                Set plage = Union(plage, Rows(curlig))
            End If
        End If

et en sortie de boucle:

    If Not plage Is Nothing Then plage.EntireRow.Delete

A+

 

[sinedv]

Re : effacer lignes cachées

un grand merci !

pour un nombre de lignes cachée comparable, je suis passé 185 s à ... 4,8 s !

j'ai bien retenu l'utilisation très efficace de Union()

Reste à creuser usedRange.rows.count ... dans mon cas, je démarre à la ligne 9

merci encore

 

[sinedv]

Re : effacer lignes cachées

pour info:

compter le nombre de ligne à partir de la ligne 9 incluse c'est

nb_lig = ActiveSheet.UsedRange.Rows("9:" & ActiveSheet.UsedRange.Rows.Count).Count

bonne journée

 

[Guest]

Re : effacer lignes cachées

Re,

usedRange.rows.count

Te donne le nombre de lignes utilisées dans la feuille.

Si tu fais une boucle For en partant de curlig=9 jusqu'à 'UsedRange.rows.count' pas de problème.

Mais une boucle Do...Loop va très bien aussi mais t'oblige à inclure un test sur une cellule(si vide) ce qui représente un coût en temps plus élevé.

De façon générale, dans une boucle longue ,moins tu fais référence aux cellules et plus c'est rapide.

Dans ta boucle originelle tu avais 3 références à la même cellule, avec en plus un 'Select' aussi inutil que gourmand en temps.

Si un jour tu es obligé d'en passé par des références multiple à une même cellule ou plage de cellule, il vaut mieux créer une référence explicite:

Dim c as Range
Dim CurLig As Long
For i =1 to 50000
  Set c=Cells(CurLig,1)
     If IsEmpty(c) then
         c.offset(,2)="toto"
         c.value=i
   End If
Next c

C'est plus rapide que d'obliger excel à calculer 3 fois Cells(CurLig,1).

A+

A+