Discussion :

[Val2000]

Bonjour,

j'ai un problème avec une boucle, elle marche bien mais est particulièrement lente. Je pense que c'est la syntaxe for each...next.
Je travaille sur un fichier avec 50 onglets et aurais besoin d'un code qui marche vite:

Voici mon essai:

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 SupprCells()
 
Dim vcell As Variant
Application.ScreenUpdating = False
Dim i As Byte
 
For i = 5 To ActiveWorkbook.Sheets.Count
ActiveWorkbook.Sheets(i).Select
Range(Cells(6, 4), Cells(87, 41)).Select
For Each vcell In Selection
If vcell.HasFormula = False And IsNumeric(vcell) = True Then
vcell.ClearContents
End If
Next
Next
End Sub

Merci de votre aide!

[Fred.dz]

Tu peux ajouter Application.screenupdating=false au début et Application.screenupdating=true. Normalement ça gagne pas mal de temps.

Après je pense que tu peux gagner du temps en définissant une plage et en faisant for each vcell in plage plutôt que de selectionner à chaque fois la plage. Parce que la selection fait perdre du temps d'après mes souvenirs.

[Invité]

En général il faut éviter tout appel à la fonction '.Select' car elle est dans la plupart des cas inutile.

Est-ce que ce code est plus rapide ?

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
Option Explicit
 
Private Sub SupprCells()
Dim rngCell As Range
Dim i As Long
 
    Application.ScreenUpdating = False
 
    For i = 5 To ActiveWorkbook.Sheets.Count
        With ActiveWorkbook.Sheets(i)
            For Each rngCell In .Range(.Cells(6, 4), .Cells(87, 41))
                If rngCell.HasFormula = False And IsNumeric(rngCell) = True Then
                    rngCell.ClearContents
                End If
            Next rngCell
        End With
    Next
 
End Sub

[Ashireon]

hello,

ca devrait accelerer

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
Sub SupprCells()
 
	Dim vcell As Variant
	Application.ScreenUpdating = False
	Dim i As Byte
 
		For i = 5 To ActiveWorkbook.Sheets.Count
			For Each vcell In ActiveWorkbook.Sheets(i).Range(Cells(6, 4), Cells(87, 41))
				If vcell.HasFormula = False And IsNumeric(vcell) = True Then
					vcell.ClearContents
				End If
			Next
		Next
Application.ScreenUpdating = true
End Sub

[Val2000]

Merci pour vos réponses.
La proposition de vcottineau ne change rien, l'ordi plante, celle d'Ashireon blocke sur la ligne 8...
Ce qui est étonnant c'est que, bien que l'ordi n'est pas le plus rapide, il exécute des macros très longues et complexes et se blocke sur ce petit bout de code...

[Ashireon]

Quelle est le message stp

[Val2000]

Erreur 1004, erreur définie par l'application ou par l'objet

[nibledispo]

bonjour,

sur la version Ashireon : peut-être déclarer vCell de type Object.

cordialement.

[Invité]

Merci pour vos réponses.
La proposition de vcottineau ne change rien, l'ordi plante, celle d'Ashireon blocke sur la ligne 8...
Ce qui est étonnant c'est que, bien que l'ordi n'est pas le plus rapide, il exécute des macros très longues et complexes et se blocke sur ce petit bout de code...

Mon code fonctionne. Tu n'as qu'a réduire le nombre de feuilles pour le vérifier. Et il s'exécute plus rapidement qu'avec ta méthode.

Tu testes environ 150 000 cellules alors c'est normal que ça prenne un peu de temps.

Il y effectivement une erreur dans le code Ashireon car les références aux cellules ne sont pas bonnes.

bonjour,

sur la version Ashireon : peut-être déclarer vCell de type Object.

cordialement.

Non. 'vCell' est de type 'Range'.

[NVCfrm]

bonjour,

d'abord le problème de lenteur ne doit pas être dû au ScreenUpdating, mais au calcul. Mets le calcul en manuel. je suppose que tes feuilles sont bourrées de formules.
réadaptation de ton 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
Sub SupprCells()
 
    Dim vcell As Range, r As Range
    'Application.ScreenUpdating = False
    Dim i As Byte
 
    Application.Calculation = -4135
    On Error Resume Next
 
    For i = 1 To ActiveWorkbook.Sheets.Count
        Set r = Range(Sheets(i).Name & "!D4:AO87").SpecialCells(xlCellTypeConstants)
        If Err = 0 Then
            For Each vcell In r.Cells
                If IsNumeric(vcell) Then vcell = ""
            Next
            Set r = Nothing
        End If
        Err = 0
    Next
 
    Application.Calculation = -4105
 
End Sub

[Val2000]

Ousmane,

avec ton code je supprimerai toutes les cellules contenants des formules...
Il y a deux conditions!

[pgz]

Bonjour.

Je te propose :

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
33
34
35
36
37
38
39
40
Sub SupprCells()
 
Dim iSaveCalculationMode As Integer
Dim bSaveUpdatingMode As Boolean
 
Dim i       As Integer
Dim vF      As Variant
Dim vV      As Variant
Dim li      As Long
Dim lj      As Integer
Dim oSh     As Excel.Worksheet
Dim oRng    As Excel.Range
 
iSaveCalculationMode = Application.Calculation
Application.Calculation = xlCalculationManual
 
bSaveUpdatingMode = Application.ScreenUpdating
Application.ScreenUpdating = False
 
For i = 1 To ThisWorkbook.Worksheets.Count
    Set oSh = ThisWorkbook.Worksheets(i)
    Set oRng = oSh.Range(oSh.Cells(6, 4), oSh.Cells(87, 41))
    vF = oRng.Formula
    vV = oRng.Value
 
    For li = 1 To UBound(vF, 1)
        For lj = 1 To UBound(vF, 2)
            If (vF(li, lj) Like "=*") And (IsNumeric(vV(li, lj))) Then vF(li, lj) = vbNullString
        Next lj
    Next li
 
    oRng.Formula = vF
Next i
 
Set oSh = Nothing
Set oRng = Nothing
Application.Calculation = iSaveCalculationMode
Application.ScreenUpdating = bSaveUpdatingMode
 
End Sub

Cordialement,

[Didier Gonard]

Bonjour,

Si tu n'as pas de dates* dans tes données, tu peux boucler sur chaque feuille en une seule ligne du genre :

Code :

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

Selection.SpecialCells(xlCellTypeConstants, 1).Clear

équivalent de sélectionner cellules, constantes, nombres de l'interface feuille.


* les dates étant des numéros de séries en sous-jacent, elles seraient supprimées aussi par ce biais ultra rapide sinon.

Le contexte exact permettrait d'autres approches peut-être sinon..

cordialement,

Didier

[patricktoulon]

Bonjour
ormonth a raison
tu pourrais utiliser les fonction native pour un gain de temps
un exemple

Code :

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

1
2
3
for i = 5 to sheets.count
sheets(i).range("a1:c30").SpecialCells (xlCellTypeFormulas, xlNumbers)=""
next

on efface toute les cellules qui ont une formule dont le resultat ne peut qu'etre numerique dans la plage de cellules a1:a30 dans le sheets (i)
i etant l'index du sheets dans la boucle
Au plaisir

[Val2000]

Merci à tous pour vos réponses.
Mon code marchait bien avec un autre ordi et un autre fichier, j'ai également testé ces codes selon vos propositions, ça marche bien aussi.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
Sub OrmonthOK()
Dim vcell As Variant
Application.ScreenUpdating = False
Dim i As Byte
 
For i = 2 To ActiveWorkbook.Sheets.Count
ActiveWorkbook.Sheets(i).Select
Range(Cells(6, 4), Cells(87, 41)).Select
Selection.SpecialCells(xlCellTypeConstants, 1).Clear
Next
End Sub

Par contre, il faut prendre

Code :

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

...(xlCellsTypeConstants, 1).clear ou = ""

je voulais justement garder les formules et le texte et juste supprimer les données en dur.
Merci à tous.

[Didier Gonard]

Bonjour,

Comme dit précédemment, et surtout quand on veut optimiser, il faut éviter tous les Select non indispensables !

Donc ceci est inutile :

Code :

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

Range(Cells(6, 4), Cells(87, 41)).Select

Ensuite...

Par contre, il faut prendre

...(xlCellsTypeConstants, 1).clear ou (xlCellsTypeConstants, 1)= ""

Clear ou = "" n’ont pas du tout les même effets

Il vaut mieux être toujours explicite
La méthode Clear appliquée à l’objet Range efface les données (constantes et formules) et les formats (dont les commentaires).
Il existe sinon pour être plus sélectif :
La méthode ClearFormats qui supprime les formats seulement
La méthode ClearContents qui supprime les données, mais préserve les formats, Range(xx) = "" est l’équivalent de Range(xx) .ClearContents.
Donc ça dépend de ce que tu veux faire…


Sinon, toujours afin d’éviter les Select et d’optimiser, il est intéressant de changer la notation de la plage de cellule, celle utilisée actuellement revient en sous-jacent à l’utilisation de la méthode Union qui demande une feuille active, une notation avec l’opérateur de plage «:» sur un range permet de s’en passer ( Mon exemple perso = 3 feuilles et Range("A1:E12")).

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Sub OrmonthOKTrue()
Dim I As Byte 'dépend du nombre de feuilles
'En VBA, regrouper les déclarations en haut de procédure si multiples, c'est plus lisible
 
'Application.ScreenUpdating = False 'devient inutile vu que pas de Select
 
For I = 1 To 3
    With Worksheets(I) 'Vu que tu cibles des feuilles de Calcul, autant employer leur  collection spécifique(pas Sheets)
        .Range("A1:E12").SpecialCells(xlCellTypeConstants, 1).Clear 'En passant par cette notation Range, on évite les Select de feuilles
        'ou ClearContents si on veut garder des formats existants
    End With
Next I
 
End Sub

cordialement,

Didier