Discussion :

[esse2016]

Bonjour au Forum,

je recherche une solution pour supprimer certaines colonnes dont je connais le nom des entêtes.
Puis ne garder que ces colonnes en supprimant les autres


actuellement j'effectue ceci mais c'est trop long.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
With ActiveSheet
    For i = .UsedRange.Column + .UsedRange.Columns.Count To 1 Step -1
        If Cells(6, i).Value <> "Niveau" And Cells(6, i).Value <> "Version" And Cells(6, i).Value <> "Référence" And Cells(6, i).Value <> "Nom" _
        And Cells(6, i).Value <> "Statut" And Cells(6, i).Value <> "Type" And Cells(6, i).Value <> "huile" And Cells(6, i).Value <> "vente" _
        And Cells(6, i).Value <> "Numr" And Cells(6, i).Value <> "region" _
        And Cells(6, i).Value <> "client" And Cells(6, i).Value <> "mod" And Cells(6, i).Value <> "Stru" _
        And Cells(6, i).Value <> "crochet" Then
        .Columns(i).Delete
        End If
    Next i
End With

sachant que mes colonnes commencent en "A3" et se termine parfois pas tout le temps en colonne "JF"

Quel piste ? si vous avez une idée je suis intéressé.

merci

[deedolith]

Salut,

As-tu essayé de désactiver l'affichage ?

Code :

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

Application.ScreenUpdating = False

Note:
Ne pas oublier sa contrepartie:

Code :

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

Application.ScreenUpdating = True

[esse2016]

bonjour deedolith,

oui bien sur cela a été intégré.

Je me demandais si avec du autofilter ça pouvait passer mais je trouve cela compliqué...

[deedolith]

Heu non, les filtres n'agissent que sur les lignes, pas sur les colonnes.

Et, Power Query ?

[Franc]

Bonjour, je propose une méthode qui consiste à créer une liste des noms de colonnes que vous souhaitez conserver, puis de supprimer toutes les autres colonnes qui ne figurent pas dans cette liste. Ce code parcourt les colonnes de la droite vers la gauche (pour éviter les problèmes de décalage lors de la suppression des colonnes), vérifie si le nom de la colonne est dans la collection des noms à conserver, et supprime la colonne si ce n’est pas le cas. Je pense que ça devrait être 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
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
Sub SupprimerColonnes()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim ColToKeep As New Collection
    Dim i As Long
 
    Set ws = ActiveSheet
 
    ' Ajouter les noms des colonnes à conserver à la collection
    ColToKeep.Add "Niveau"
    ColToKeep.Add "Version"
    ColToKeep.Add "Référence"
    ColToKeep.Add "Nom"
    ColToKeep.Add "Statut"
    ColToKeep.Add "Type"
    ColToKeep.Add "huile"
    ColToKeep.Add "vente"
    ColToKeep.Add "Numr"
    ColToKeep.Add "region"
    ColToKeep.Add "client"
    ColToKeep.Add "mod"
    ColToKeep.Add "Stru"
    ColToKeep.Add "crochet"
 
    ' Parcourir les colonnes de la droite vers la gauche
    For i = ws.Cells(6, ws.Columns.Count).End(xlToLeft).Column To 1 Step -1
        ' Vérifier si le nom de la colonne est dans la collection
        On Error Resume Next
        Set rng = ColToKeep(ws.Cells(6, i).Value)
        On Error GoTo 0
 
        ' Si le nom de la colonne n'est pas dans la collection, supprimer la colonne
        If rng Is Nothing Then
            ws.Columns(i).Delete
        End If
 
        ' Réinitialiser la variable pour la prochaine itération
        Set rng = Nothing
    Next i
End Sub

[Tête de chat]

Bonjour,

En plus du screenUpdating =False qui va t'économiser la mise à jour de l'écran, il faut réduire le nombre de delete.

À chaque delete, quoi qu'on fasse, Excel met à jour les formules pour refléter le décalage que tu viens de faire. Si ton classeur est gros, et que tu fais beaucoup de suppressions, ça peut prendre du temps.
La bonne technique pour gagner en performance, c'est de faire les delete en bloc (supprimer une colonne ou mille colonnes prend le même temps si c'est mille colonnes ensemble.

Dans ton cas, tu gardes 14 colonnes donc tu devrais pouvoir passer à une quinzaine de delete max au lieu de 250.

Ça pourrait ressembler à :

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
Dim I As Long, Deb As Long, Fin As Long
With ActiveSheet
    For I = .UsedRange.Column + .UsedRange.Columns.Count To 1 Step -1
        If .Cells(6, I).Value <> "Niveau" And .Cells(6, I).Value <> "Version" And .Cells(6, I).Value <> "Référence" And .Cells(6, I).Value <> "Nom" _
        And .Cells(6, I).Value <> "Statut" And .Cells(6, I).Value <> "Type" And .Cells(6, I).Value <> "huile" And .Cells(6, I).Value <> "vente" _
        And .Cells(6, I).Value <> "Numr" And .Cells(6, I).Value <> "region" _
        And .Cells(6, I).Value <> "client" And .Cells(6, I).Value <> "mod" And .Cells(6, I).Value <> "Stru" _
        And .Cells(6, I).Value <> "crochet" Then
            If Fin = 0 Then Fin = I ' on débute un nouveau bloc, de droite à gauche donc on a sa dernière colonne
        Else
            ' on est sur une bonne colonne, si un bloc était en cours, la précédente termine le bloc
            ' (donc la première colonne du bloc puisqu'on va de D à G)
            If Fin <> 0 Then Deb = I + 1
        End If
        If Deb <> 0 Then 'on vient de finir de déterminer un bloc
            .Range(.Columns(Deb), .Columns(Fin)).Delete
            ' et on réinitialise en attendant le prochain bloc
            Deb = 0
            Fin = 0
        End If
    Next I
    ' Dernier delete
    If Fin > 0 Then .Range(.Columns(1), .Columns(Fin)).Delete
End With

Nb : j'ai mis des "." devant Cells, tant qu'à utiliser un with activesheet autant utiliser les références explicites jusqu'au bout.

[Pierre Fauconnier]

Salut.

Sauf dans un cas très tordu, Application.ScreenUpdating = True n'est pas utile. L'écran est automatiquement rafraichi lorsque le code repasse la main à Excel.

[Pierre Fauconnier]

[...]
À chaque delete, quoi qu'on fasse, Excel met à jour les formules pour refléter le décalage que tu viens de faire[...]

Non pas "quoi qu'on fasse". Normalement, on doit passer en application.Calculation = xlCalculationManual avant de supprimer et restaurer le mode de calcul initial après. Ainsi, il n' a pas de recalcul à chaque Delete.

[Tête de chat]

Re,

je ne parle pas de recalcul des formules mais de mise à jour des formules. Tu supprimes la colonne B, la formule qui était =E1 devient =D1. Bien sûr ça n'est pas stocké comme ça en mémoire, mais il se passe des choses quand on supprime une ligne ou une colonne qui ne relèvent pas du recalcul, donc pas bloquées par le Calculation = xlCalculationManual, et qui prennent un temps fou.
Donc je maintiens qu'en faisant les suppressions par bloc, toutes choses égales par ailleurs, on gagne un temps fou.

[deedolith]

@Tête de chat:
Sauf erreur de ma part, il est possible de définir une plage sur plusieurs cellules, même non contiguës.
Exemple:

Code :

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

Range($N:$N,$R:$R)

Ce qui, une fois les plages indésirables calculées, pourrait réduire les opération de suppression à une seule.

Y aurait-il un gain ?

[Pierre Fauconnier]

[...]
Donc je maintiens qu'en faisant les suppressions par bloc, toutes choses égales par ailleurs, on gagne un temps fou.

Je n'ai pas dit le contraire 😉

[esse2016]

Merci à chacun pour vos interventions.

La méthode de Tête de Chat fonctionne est beaucoup plus rapide que la mienne
La méthode de Franc est trop longue et boucle à l'infini

A voir donc
mais merci

[Tête de chat]

Bonjour,

Ce qui, une fois les plages indésirables calculées, pourrait réduire les opération de suppression à une seule.
Y aurait-il un gain ?

Bonne question...

Parti d'un fichier d'environ 220 colonnes x 3500 lignes, j'a comparé les 3 méthodes : 1 à une, bloc par bloc et en union, pour supprimer 9 colonnes sur 10.

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
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
Option Explicit
Sub test1()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Debug.Print Time
Dim i As Long
With ActiveSheet
For i = 210 To 1 Step -1
    If (i Mod 10) <> 0 Then
        .Columns(i).Delete
    End If
Next i
End With
Debug.Print Time
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
Sub test2()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Debug.Print Time
Dim i As Long
With ActiveSheet
For i = 210 To 0 Step -1
    If (i Mod 10) = 0 Then
        .Range(.Columns(i + 1), .Columns(i + 9)).Delete
    End If
Next i
End With
Debug.Print Time
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
 
Sub test3()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Debug.Print Time
Dim i As Long, MaRange As Range
With ActiveSheet
For i = 210 To 1 Step -1
    If (i Mod 10) <> 0 Then
        If MaRange Is Nothing Then
            Set MaRange = .Columns(i)
        Else
            Set MaRange = Union(MaRange, .Columns(i))
        End If
    End If
Next i
End With
MaRange.Delete
Debug.Print Time
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Au départ la première méthode prenait 4s j'ai donc ajouté des formules pour compliquer et ça donne :

Méthode 1 : 32s
Méthode 2 : 4s
Méthode 3 : 4s

Avec la précision du test, pas de différence ; possible qu'Excel procède par sous-range (le union a bien regroupé des plages de 9 colonnes, comme on peut le vérifier en regardant MaRange.Addresss). La méthode suggérée par deedolith a l'avantage d'être plus simple à coder et maintenir.

Je laisse à d'autres le soin de pousser les tests plus loin