macro trop lente

**ilissnmiden92** · 17/07/2015, 16h07

Bonjour j'ai besoin de votre aide,

J'utilise une macro qui suite à une recherche V, me vire toutes lignes ou ya des #N/A, mais là étant donné qu'elle doit virer 7000 lignes, elle met bcp de temps à finir.
Est ce qu'il y a pas une astuce, un code à intégrer pour qu'elle devienne rapide meme en cas d'un nombre important de lignes

voici le code utilisé:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
Sub suppressionLigne_SiErreur_NA()
    Dim x As Long
 
    For x = 65536 To 1 Step -1
 
        If WorksheetFunction.IsError(Range("AF" & x)) = True Then
            If CVErr(xlErrNA) = Range("AF" & x) Then _
                Rows(x).EntireRow.Delete
        End If
    Next x
 
End Sub

Merci pour votre aide

**Marc-L** · 17/07/2015, 16h17

Bonjour,

utiliser une boucle est souvent ce qu'il y a de plus lent !

Plus rapide est d'utiliser un filtre puis de supprimer les lignes filtrées.
Et encore plus rapide est de trier auparavant les lignes sur la colonne critère du filtre …

_____________________________________________________________________________________________________
Je suis Charlie, Bardo, Sousse

**ilissnmiden92** · 17/07/2015, 16h21

C'est ce que j'ai fait au début, mais comme je l'ai fait juste avec l'enregistreur de macro, ca ma fait un énorme code
j'ai pas faire un code pour dire ca en deux lignes..

peux tu m'aider
merci

**Qwazerty** · 17/07/2015, 16h28

Salut

en gardant la trame de ton code, essai comme cela.

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 suppressionLigne_SiErreur_NA()
    Dim x As Long
 
    'On bloque le rafraichissement (gain de temps)
    Application.ScreenUpdating = False
 
    'On pointe la feuille sur laquelle on travaille
    With Feuil1
 
        'On boucle de la dernière cellule non vide de la colonne AF à la 1ère cellule
        For x = .Cells(.Rows.Count).End(xlUp).Rows To 1 Step -1
            If IsError(.Cells(x, "AF")) = True Then
                If CVErr(xlErrNA) = .Cells(x, "AF") Then .Cells(x, "AF").EntireRow.Delete 'ou .rows(x).delete
            End If
        Next x
 
    Application.ScreenUpdating = True
End Sub

[Edit]

Sinon une autre solution (pas testée) à base de specialcells et de find pour limiter le temps de boucle mais je doute qu'il y ait un gros écart.

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
Sub SuppSiErr()
Dim aCell As Range, TheCells As Range
 
    'On bloque le rafraichissement (gain de temps)
    Application.ScreenUpdating = False
 
    'On pointe la feuille
    With Feuil1
 
        'On pointe toutes les cellules contenant une formule
        Set TheCells = .Columns("AF").SpecialCells(xlCellTypeFormulas)
 
        'On recherche parmis ces cellule celles contenant une erreur
        Set aCell = TheCells.Find("#N/A", , , xlwholes)
 
        'On regarde si une correspondance a été trouvée et on boucle sur les autres cellules
        Do Until aCell Is Nothing
            'On supprime la ligne
            aCell.EntireRow.Delete
 
            'On continue la recherche
            Set aCell = TheCells.FindNext(aCell)
        Loop
    End With
 
    Application.ScreenUpdating = True
End Sub

Pour l'idée du filtre, tu peux à la suite du filtre utiliser Specialcells pour ne pointer que les cellules visibles et les supprimer d'un coup. Je n'aime pas trop l'utilisation du filtre, je laisse à Marc le soin de te proposer un code si le coeur lui en dit

[/Edit]
++
Qwaz

**ilissnmiden92** · 17/07/2015, 16h39

J'ai essayé ton code, j'ai un message d'erreur, "objet requis"

**Qwazerty** · 17/07/2015, 16h42

SAlut

Quelle ligne ?

Code :

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

With Feuil1

je suppose, il faut adapter et mettre le codename de ta feuille

J'ai édité le message précédent.

++
Qwaz

**ilissnmiden92** · 17/07/2015, 17h03

J'ai bien mis le nom de mon onglet, faut il mettre un code en particulier?

**Qwazerty** · 17/07/2015, 17h10

Salut

Désolé, j'avais oublié de fermer la balise With

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 suppressionLigne_SiErreur_NA()
    Dim x As Long
 
    'On bloque le rafraichissement (gain de temps)
    Application.ScreenUpdating = False
 
    'On pointe la feuille sur laquelle on travaille
    With Feuil1
 
        'On boucle de la dernière cellule non vide de la colonne AF à la 1ère cellule
        For x = .Cells(.Rows.Count).End(xlUp).Rows To 1 Step -1
            If IsError(.Cells(x, "AF")) = True Then
                If CVErr(xlErrNA) = .Cells(x, "AF") Then .Cells(x, "AF").EntireRow.Delete 'ou .rows(x).delete
            End If
        Next x
    End With
    Application.ScreenUpdating = True
End Sub

++
Qwaz

**ilissnmiden92** · 17/07/2015, 17h12

ca me met objet requis à cette ligne

Code :

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

  For x = .Cells(.Rows.Count).End(xlUp).Rows To 1 Step -1

**patricktoulon** · 17/07/2015, 17h27

bonjour qwazerty
ceci génère une erreur si il n'y as pas de formule ou si il y a aussi des "constants"
'On pointe toutes les cellules contenant une formule

Code :

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

    Set TheCells = .Columns("AF").SpecialCells(xlCellTypeFormulas)

pour utiliser specialcell de cette manière il faut tester d'abords

Code :

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

if  .Columns("AF").hasformulas renvoie faux si c'est non  ou null ou si il y a les 2

**Qwazerty** · 17/07/2015, 17h32

Salut

... voila ce que c'est de ne pas tester les codes que l'on donne :p
c'est rows qui pose problème, il faut mettre row

Code :

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

  For x = .Cells(.Rows.Count).End(xlUp).Row To 1 Step -1

Je vais essayer de faire un petit bout de tableau pour tester mes codes

**ilissnmiden92** · 17/07/2015, 17h44

j'ai testé toujours le meme msg d'erreur

**Qwazerty** · 17/07/2015, 17h46

Salut

Voila celui la est testé :p et corrigé...

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
32
Sub SuppSiErr()
Dim aCell As Range, TheCells As Range
 
    'On bloque le rafraichissement (gain de temps)
    Application.ScreenUpdating = False
 
    'On pointe la feuille
    With Feuil2
 
        'On bloque la gestion d'erreur
        On Error Resume Next
        'On pointe toutes les cellules contenant une formule
        Set TheCells = .Columns("AF").SpecialCells(xlCellTypeFormulas)
        'On réactive la gestion d'erreur et on control le contenu de TheCells
        On Error GoTo 0
        If Not TheCells Is Nothing Then
            'On recherche parmis ces cellule celles contenant une erreur
            Set aCell = TheCells.Find("#N/A", , , xlWhole)
 
            'On regarde si une correspondance a été trouvée et on boucle sur les autres cellules
            Do Until aCell Is Nothing
                'On supprime la ligne
                aCell.EntireRow.Delete
 
                'On continue la recherche
                Set aCell = TheCells.FindNext()
            Loop
        End If
    End With
 
    Application.ScreenUpdating = True
End Sub

[Edit]
Et son petit frère corrigé aussi !

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 suppressionLigne_SiErreur_NA2()
    Dim x As Long
 
    'On bloque le rafraichissement (gain de temps)
    Application.ScreenUpdating = False
 
    'On pointe la feuille sur laquelle on travaille
    With Feuil2
 
        'On boucle de la dernière cellule non vide de la colonne AF à la 1ère cellule
        For x = .Cells(.Rows.Count, "AF").End(xlUp).Row To 1 Step -1
            If IsError(.Cells(x, "AF")) = True Then
                If CVErr(xlErrNA) = .Cells(x, "AF") Then .Cells(x, "AF").EntireRow.Delete 'ou .rows(x).delete
            End If
        Next x
    End With
    Application.ScreenUpdating = True
End Sub

Navré pour les allers-retours

++
Qwaz

**ilissnmiden92** · 17/07/2015, 17h54

toujours le meme message d'erreur au meme endroit, désolée jtai trop fait travailler

**Qwazerty** · 17/07/2015, 17h57

Salut

La je ne comprends pas, cette fois j'ai testé le code et il fonctionne.
tu intègres ce code dans un autre code ? Si c'est le cas coller ici le code global que tu utilises.
Quel est le message d'erreur que renvoi VBA.

... va falloir que je me change les idées, je surchauffe

++
Qwaz

**patricktoulon** · 17/07/2015, 18h31

ouais ca surchauffe ici !!!!!
qwazerty :findnext(Quoi?)

Code :

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

1
2
3
4
5
6
7
8
DoUntil aCell IsNothing
                'On supprime la ligne
                aCell.EntireRow.Delete
 
                'On continue la recherche
                Set aCell = TheCells.FindNext()
            Loop

**Qwazerty** · 17/07/2015, 18h32

Ben FindNext rien Patrick, regarde l'aide.

**patricktoulon** · 17/07/2015, 18h39

a oui!!!!!! puré je faisait la même erreur avant
dans une boucle find on ne peut pas supprimer la cell en variable car si on la supprime dans le find next la variable est nothing apres

c'est vrai puré
moi aussi j'ai chauffé

**patricktoulon** · 17/07/2015, 18h49

oui justement dans l'aide c'est .findnext(c)
exemple de code dans l'aide

Code :

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

1
2
3
4
5
6
7
8
9
10
With Worksheets(1).Range("a1:a500")
    Set c = .Find(2, lookin:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            c.Value = 5
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With

en plus oui la ta surchauffer
avec l'enregistreur de macro je selectionne les colonnes A a F
ca donne

Code :

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

Columns("A:F").Select

j'avais un doute c'est pour ca que je l'ai fait avec l'enregistreur ,pour etre sur
dans ton code c'est

Code :

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

Set TheCells = .Columns("AF").SpecialCells(.....)

et comme argument afin d'eviter l'erreur "constants/formula" je mettrais " xllastcell"
bon j'ai chaud moi

**Marc-L** · 17/07/2015, 19h05

Envoyé par ilissnmiden92

C'est ce que j'ai fait au début, mais comme je l'ai fait juste avec l'enregistreur de macro, ca ma fait un énorme code
j'ai pas faire un code pour dire ca en deux lignes..

peux tu m'aider
merci

Poste ton code balisé (conformément aux règles du forum via l'icône #)
accompagné d'un classeur exemple au format xlsx (sans code) …

macro trop lente

Macros et VBA Excel

Discussions similaires

Partager

Partager