Discussion :

[nubed]

Bonjour a tous,

J’ai mis en place une macro qui me permet de supprimer des lignes selon des conditions.

Mon problème vient de la taille des fichiers Excel qui pour certains dépassent les 60.000 lignes. Du coup je me prends un message d’erreur « Erreur d’exécution ‘6’ dépassement de capacité ».

J’ai triche provisoirement en divisant le fichier en deux mais bon… Par la suite je vais avoir des fichiers Excel volumineux.

Je pense à un problème d’affectation des variables. Qu’en dites-vous ?

Merci d’avance.

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 DEL_INGP()
'
' Format_file Macro
'
Application.ScreenUpdating = False
 
Dim targetSheet As Worksheet
Dim i As Integer
 
'
On Error GoTo Openfilerror
Workbooks.Open Application.GetOpenFilename(Title:="Please select the file with the INGP counterpart (xls file)")
On Error GoTo 0
 
i = 2
While Not IsEmpty(Cells(i, 16))
i = i + 1
Wend
 
For i = 2 To i
    If Cells(i, 16).Value = "INGP" Then
    Cells(i - 1, 16).EntireRow.Delete
    End If
Next i
 
  MsgBox "INGP counterparts have been deleted"
 
Openfilerror:
    Exit Sub
End Sub

Cordialement

[Invité]

Précises la ligne de l'erreur!

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 DEL_INGP()
'
' Format_file Macro
'
Application.ScreenUpdating = False
Dim Wb As Workbook 'on déclare un objet classeur!
Dim targetSheet As Worksheet
Dim i As Long
Dim R As Range
'
On Error GoTo Openfilerror
'On ouvre et on affect le classeur à la variable WB
Set Wb = Workbooks.Open(Application.GetOpenFilename(Title:="Please select the file with the INGP counterpart (xls file)"))
On Error GoTo 0
 
'i = 2
'While Not IsEmpty(Cells(i, 16))
'i = i + 1
'Wend
'On récupère toutes les cellules non vides !
 Set R = Wb.ActiveSheet.UsedRange
For i = R.Rows.Count To 2 Step -1 'quand on suprime des ligne on commence par la fin!
    If R(i, 16).Value = "INGP" Then
     R(i - 1, 16).EntireRow.Delete
    End If
Next i
 
  MsgBox "INGP counterparts have been deleted"
 
Openfilerror:
    Exit Sub
End Sub

[el_slapper]

Code :

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

For i = 2 To i

???????????????????????????

Tu fais varier la valeur de i entre 2 et lui-même????? Ca ne peut donner que des résultats fortement aléatoires.

Sinon, il faut savoir si tu as EXCEL 2003(ou antérieur), ou EXCEL 2007(ou postérieur). A partir de 2007, on peut monter à 1 million de lignes, ça laisse de la place.

Quoiqu'il en soit, je vais partir du principe que tu as 2007(ou postérieur). Pour 2003(ou antérieur), ça serait plus compliqué.
(1)Le calcul de ta limite basse est à revoir. Puisque tu sembles t'arrêter à la première case non vide de la colonne 16, alors

Code :

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

1
2
Dim DerLigne as Long
DerLigne = Range("P2").End(xlDown).Row

(2)Tu supprimes la ligne précédente quand tu as une ligne INGP. Il faut utiliser une autre variable que la limite de ligne.

Code :

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

1
2
3
4
5
6
Dim Ligne as Long
For Ligne = 2 TO DerLigne
    If Cells(Ligne , 16).Value = "INGP" Then
        Cells(Ligne  - 1, 16).EntireRow.Delete
    End If
Next Ligne

Ne marche que si tu n'as jamais 2 lignes INGP qui se suivent(c'est le cas?)

La clef quand même, c'est de définir ses variables en Long(ça permet de dépasser 60000) et d'avoir 2 variables différentes(limite et compteur).



EDIT : @rdrupt : tu as tort, dans son cas. Parcequ'il efface la ligne précédente. Donc, si la ligne 16 contient INGP, alors il efface la ligne 15. Et INGP monte en ligne 15. Mais alors ton compteur est en ligne 15, et il recommence. En fait, tu lui efface tout.

Dans le cas général(on efface la ligne que l'on est en train d'analyser), tu as 100% raison. Et ta boucle qui se passe de variable intermédaire marche très bien. Juste, là, non.

[nubed]

Merci pour vos réponses, quelques précisions je suis sur excel 2010. Mon code marche et ce même quand deux lignes se suivent.

Je suis débutant en VBA, en général je récupère des codes que je « bricole ».

J’ai teste ton code @el_Slapper il marche effectivement. Du coup j’ai réessayé avec le « mien » en changeant Integer par Long et j’obtiens le même résultat qu’avec le tiens.

En revanche ton code est beaucoup clair, j’ai du mal à cerner les deux variables différentes auxquelles tu fais référence (limite et compteur)

Code :

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

DerLigne = Range("P2").End(xlDown).Row

Ici ce serait la limite, donc à partir de P2 (explicite) et End(x1Down).Row c’est jusqu’à temps qu’une cellule de la colonne soit vide ?

Code :

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

1
2
3
4
5
6
7
8
9
10
11
Sub INGP()
Application.ScreenUpdating = False
Dim DerLigne As Long
DerLigne = Range("P2").End(xlDown).Row
Dim Ligne As Long
For Ligne = 2 To DerLigne
    If Cells(Ligne, 16).Value = "INGP" Then
        Cells(Ligne - 1, 16).EntireRow.Delete
    End If
Next Ligne
End Sub

Bien cordialement,

[el_slapper]

(.../...)Ici ce serait la limite, donc à partir de P2 (explicite) et End(x1Down).Row c’est jusqu’à temps qu’une cellule de la colonne soit vide ?

Exactement. En fait, pour savoir comment j'ai trouvé, il suffit de lancer l'enregistreur de macro, de mettre le curseur en P2, d'appuyer sur [Ctrl] et [flèche bas]. Tu arrêtes l'enregistrement, et le code te donne :

Code :

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

1
2
    Range("P2").Select
    Selection.End(xlDown).Select

Qui se simplifie en

Code :

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

Range("P2").End(xlDown).Select

Or tu n'as pas besoin de selectionner la case, juste d'avoir son numéro de ligne - ta limite basse : Donc on remplace l'action Select par la propriété Row. Que j'alimente dans une donnée "Derligne", qui contient le numéro de la dernière ligne à trouver. Mon compteur est la variable "Ligne" (qui tient le même rôle que i dans ton code ou celui de rdrupt).

rdrupt utilise une méthode assez sympa aussi, mais plus englobante : il va chercher la dernière ligne de ton classeur ou tu as quelquechose avec son

Code :

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

1
2
Set R = Wb.ActiveSheet.UsedRange
For i = R.Rows.Count To 2 Step -1

qui est plus élégant que mon code(sauf que dans ton cas, il faut bien le faire de haut en bas, et donc For i = 2 To R.Rows.Count.

Il évite aussi de mettre cette limite dans une variable, et l'utilise directement. C'est une question de choix, de style.

Si tu as des lignes à blanc que tu dois traiter aussi, la solution de rdrupt est ce qu'il te faut. Si tu dois t'arrêter au premier blanc de la colonne P, alors c'est bien ma solution qu'il faut prendre.(le plus probable est que les deux conviennent).



(Conclusion 1) : le vrai problème était que ton i n'était pas déclaré, et commençant à 2, était inféré en integer. La limite d'integer, c'est 32 767. En le forçant en double, tu peux monter à 2 147 483 647 lignes. Ca laisse de la marge.

(Conclusion 2) : ça suffit pour faire marcher ton code, mais j'ai fait des tests en pas-à-pas sur ton "for i = 2 to i", ça se comporte à peu près bien. Mais il peut y avoir des effets de bord, euh, désagréables. Pour rester poli. Par exemple, si ta dernière ligne est la ligne 5, comme i a servi de compteur, après la boucle, i = 6. Et si tu as encore besoin, tu est chocolat, parcequ'il a changé de valeur.

(Conclusion 3) : un détail. Comme tu mets Application.ScreenUpdating = False au début de ton code(un excellent reflexe), il faut mettre Application.ScreenUpdating = True à la fin de ton code, pour être propre.

[nubed]

Merci pour ton temps

Bonne continuation

[casefayere]

Bonjour,

Perso, je garderais l'idée de Robert (dès qu'on supprime des lignes) de commencer la boucle à la fin (mais je n'ai pas tout suivi) avec un petit changement

Code :

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

1
2
3
4
5
6
7
...
For Ligne = DerLigne - 1 To 2 Step -1
  If Cells(Ligne + 1, 16).Value = "INGP" Then
      Cells(Ligne, 16).EntireRow.Delete
  End If
Next Ligne
...