Fonction Left() sur une colonne

**cha_choux** · 24/01/2018, 12h04

Bonjour,

Je me pose ce matin une question en constatant la lenteur de mon code : est il possible d'exécuter la fonction Left() sur une colonne sans passer par une boucle ?

Par exemple pour supprimer des doublons en un fragment de seconde, nous pouvons utiliser ce code :

Code :

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

1
2
3
4
 
Columns("A:B").Select
ActiveSheet.Range("$A$1:$B$20108").RemoveDuplicates Columns:=Array(1, 2), _
       Header:=xlYes

Y a t'il un code similaire pour remplacer un Left() qui utilise une boucle for ou do while ?

Code :

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

1
2
3
4
 
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
      Cells(i, 2) = Left(Cells(i, 2), 3)
Next

Merci d'avance pour votre retour

**Menhir** · 24/01/2018, 12h11

Envoyé par cha_choux

est il possible d'exécuter la fonction Left() sur une colonne sans passer par une boucle ?

Non

Invité · 24/01/2018, 12h29

Code :

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

1
2
3
4
5
6
7
Sub Test()
Range("A2:A100") = "Toto TITI"
Columns("A:A").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A2:A100").FormulaR1C1 = "=left(RC[1],4)"
Range("A2:A100").Value = Range("A2:A100").Value
Columns("B:B").Delete Shift:=xlToLeft
End Sub

**patricktoulon** · 24/01/2018, 13h04

bien vu robert

**cha_choux** · 24/01/2018, 13h56

Ouuuuahhh trop fort les garcons !!

Je passe de 40 secondes d'exécution à 25 secondes pour la manipulation de cet Excel (je dois effectuer pas mal d'étapes afin de passer d'une 50aine de colonnes et 27000 lignes, à 2 colonnes et 2000 lignes...)

Merci beaucoup

**Marc-L** · 24/01/2018, 14h03

Envoyé par cha_choux

Y a t'il un code similaire pour remplacer un Left() qui utilise une boucle for ou do while ?

Oui et directement sans colonne supplémentaire via une formule de calculs au sein de la puissante fonction Evaluate !

___________________________________________________________________________________________________________
Je suis Paris, Egypte, Nigeria, New-York, Mogadicio, Barcelone, London, Manchester, Stockholm, Istanbul, Berlin, Nice, Bruxelles, Charlie, …

Invité · 24/01/2018, 14h04

Code :

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

1
2
3
4
5
6
7
Sub test()
With CreateObject("Adodb.Connection")
    .Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=YES;"""
    .Execute "Update [Feuil1$] set [Mon Champ]=Left([Mon Champ],4)"
    .Close
End With
End Sub

**Pierre Fauconnier** · 24/01/2018, 14h07

Envoyé par dysorthographie

Code :

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

1
2
3
4
5
6
7
Sub Test()
Range("A2:A100") = "Toto TITI"
Columns("A:A").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A2:A100").FormulaR1C1 = "=left(RC[1],4)"
Range("A2:A100").Value = Range("A2:A100").Value
Columns("B:B").Delete Shift:=xlToLeft
End Sub

Hum... Et si on a des données en dessous de A100, on fait quoi?

Attention à ces solutions qui paraissent miraculeuses mais qui peuvent casser complètement un classeur. Toujours bien penser aux conséquences avant d'employer des méthodes qui ne tiennent pas compte de la configuration du classeur et qui ne fonctionnent que dans un cas précis...

J'ai bouclé sur 100.000 lignes en traitant directement les cellules => 10 secondes (Tu as 100.000 lignes dans ton tableau?).

Pour accélérer, j'ai utilisé un tableau VBA interne à la fonction de traitement => 1 seconde...

Est-ce vraiment utile de risquer de démolir son classeur à coups d'insertion et de suppression de colonnes pour ce genre de traitements?

A titre d'exemple, voici les fonctions utilisées

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
Sub DelCells()
  Dim Debut As Date
  Dim Fin As Date
  Dim Cell As Range
 
  Debut = Now()
  For Each Cell In Range("tableau1")
    Cell.Value = Left(Cell, 4)
  Next
  Fin = Now()
  Debug.Print "Cellules: " & Format(Fin - Debut, "h:mm:ss")
End Sub
 
Sub DelTab()
  Dim Debut As Date
  Dim Fin As Date
  Dim Values
  Dim i As Long
 
  i = 0
  Debut = Now()
  Values = Range("tableau13").Value
  For i = LBound(Values) To UBound(Values)
    Values(i, 1) = Left(Values(i, 1), 4)
  Next i
  Range("tableau13").Value = Values
  Fin = Now()
  Debug.Print "Tableau: " & Format(Fin - Debut, "h:mm:ss")
End Sub

Invité · 24/01/2018, 14h11

Ici c'est un exemple pour la compréhension, car j'imagine que "Toto TITI" ne l’intéresse pas!

**Pierre Fauconnier** · 24/01/2018, 14h16

Envoyé par cha_choux

Ouuuuahhh trop fort les garcons !!

Je passe de 40 secondes d'exécution à 25 secondes pour la manipulation de cet Excel (je dois effectuer pas mal d'étapes afin de passer d'une 50aine de colonnes et 27000 lignes, à 2 colonnes et 2000 lignes...)

Merci beaucoup

Tu aurais intérêt à désactiver le calcul automatique en début de fonction puis à le remettre en fin de fonction. Idem pour le rafraichissement d'écran. Ca fait gagner beaucoup de temps à la macro.

**cha_choux** · 24/01/2018, 14h21

Pierre, ce code est exécuter à partir d'Access, l'application Excel à ce moment la cachée (xlAPP:Visible = False)
J'imagine que ca inclus la désactivation du calcul automatique et du rafraichissement de l'écran

Pour la fonction de dysorthographie je l'ai adaptée à mon besoin, voici le code :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
   TitleCellsB = Cells(1, 2)
    j = Cells(Rows.Count, 1).End(xlUp).Row
 
    Columns("B:B").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("B2:B" & j).FormulaR1C1 = "=left(RC[1],3)"
    Range("B2:B" & j).Value = Range("B2:B" & j).Value
    Columns("C:C").Delete Shift:=xlToLeft
 
    Cells(1, 2) = TitleCellsB

A ce stade de la manipulation de l'Excel, il ne reste 2 colonnes et 3000 lignes, utiliser la colonne C ne me gène pas

**Pierre Fauconnier** · 24/01/2018, 14h25

Envoyé par cha_choux

Pierre, ce code est exécuter à partir d'Access, l'application Excel à ce moment la cachée (xlAPP:Visible = False)
J'imagine que ca inclus la désactivation du calcul automatique et du rafraichissement de l'écran[...]

Je ne vois pas ce qui ferait que le calcul automatique soit désactivé si l'application est invisible. Les deux ne sont pas liés.

A mon avis, le rafraichissement d'écran non plus, même si on pourrait penser que cette optimisation a été pensée par les concepteurs d'Excel (j'en doute, ceci dit).

**cha_choux** · 24/01/2018, 14h28

Voici le code global de cette manipulation

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
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
 
    Dim xlApp As Excel.Application
    Dim xlWb As Excel.Workbook
    Dim xlSh As Excel.Worksheet
 
    Set xlApp = New Excel.Application
    xlApp.Visible = False
 
    'on ouvre le fichier + on se positionne sur la premiere feuille
    Set xlWb = xlApp.Workbooks.Open("O:\Portfolio Management\Contrôle et suivi d'activité\Suivi Target Market\20170122-TM-LU.csv", True)
    Set xlSh = xlWb.Worksheets(1)
 
 
    '*************************************************************************************************
    ' On manipule l'extraction pour conserver uniquement les données nécéssaire
    '*************************************************************************************************
    'Rows("1:3").Select
    'Selection.Delete Shift:=xlUp 'On supprime les 3 premieres lignes
 
    xlSh.Range("A:F").Delete 'On supprime tour à tour les colonnes inutiles
    xlSh.Range("B:AE").Delete
    xlSh.Range("C:F").Delete
 
    '************************************************************************************************
    ' On supprime les ISIN vide
    '*************************************************************************************************
    Range("A1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$B$23221").AutoFilter Field:=1, Criteria1:="="
    Rows("2:2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    ActiveSheet.Range("$A$1:$B$20108").AutoFilter Field:=1
    Cells.Select
    Selection.AutoFilter
    Range("A3").Select
 
    '*************************************************************************************************
    ' On supprime les doublons
    '*************************************************************************************************
    Columns("A:B").Select
   ActiveSheet.Range("$A$1:$B$20108").RemoveDuplicates Columns:=Array(1, 2), _
        Header:=xlYes
 
    '*************************************************************************************************
    ' On conserve uniquement les numeriques dans le Profil
    '*************************************************************************************************
    TitleCellsB = Cells(1, 2)
    j = Cells(Rows.Count, 1).End(xlUp).Row
 
    Columns("B:B").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("B2:B" & j).FormulaR1C1 = "=left(RC[1],3)"
    Range("B2:B" & j).Value = Range("B2:B" & j).Value
    Columns("C:C").Delete Shift:=xlToLeft
 
    Cells(1, 2) = TitleCellsB
 
    '*************************************************************************************************
    ' On concatene les donnees
    '*************************************************************************************************
    Columns("A:B").Select
    ActiveWorkbook.Worksheets("20170122-TM-LU").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("20170122-TM-LU").Sort.SortFields.Add Key:=Range( _
        "A2:A10000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("20170122-TM-LU").Sort.SortFields.Add Key:=Range( _
        "B2:B10000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("20170122-TM-LU").Sort
        .SetRange Range("A1:B10000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
 
    i = 2
    Do While Cells(i, 1) <> ""
        If Cells(i, 1) = Cells(i + 1, 1) Then
            Cells(i, 2) = Cells(i, 2) & ", " & Cells(i + 1, 2)
            Rows(i + 1 & ":" & i + 1).Select
                Selection.Delete Shift:=xlUp
        Else
            i = i + 1
        End If
    Loop
 
    '*************************************************************************************************
    ' On sauvegarde les données sur l'Excel Liste Valeurs Profils Gestion LU
    '*************************************************************************************************
    xlApp.DisplayAlerts = False 'Desactiver les alertes
        ActiveWorkbook.SaveAs FileName:= _
        "O:\Portfolio Management\Contrôle et suivi d'activité\Suivi Target Market\Liste Valeurs Profils Gestion LU.xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    xlApp.DisplayAlerts = True 'Desactiver les alertes
 
   '*************************************************************************************************
    ' On ferme l'Excel sans sauvegarder
    '*************************************************************************************************
    xlWb.Close False 'On clôture Extract base clients en sauvegardant
    Set xlWb = Nothing 'On libere xlWB
    xlApp.Quit 'On ferme l'application Excel
    Set xlApp = Nothing 'On libere xlApp
 
    '*************************************************************************************************
    ' On met à jour la table T_Extraction Donnees Profils Gestion
    '*************************************************************************************************
    DoCmd.Close acTable, "T_Extraction Donnees Profils Gestion" 'On ferme la table T_Informations Instruments
    Call CurrentDb.Execute("DELETE * FROM [T_Extraction Donnees Profils Gestion];")
 
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "T_Extraction Donnees Profils Gestion", "O:\Portfolio Management\Contrôle et suivi d'activité\Suivi Target Market\Liste Valeurs Profils Gestion LU.xlsx", True

Ce qui prend du temps est la concaténation des ligne ensembles, mais la pas le choix d'utiliser une boucle

PS : je m'excuse par avance si mon code vous fait saigner des yeux

PS2: je viens de voir que je n'ai pas défini la fin des lignes dans tout mon code, je vais le rajouter sur ma MAJ

**cha_choux** · 24/01/2018, 14h30

Envoyé par Pierre Fauconnier

Je ne vois pas ce qui ferait que le calcul automatique soit désactivé si l'application est invisible. Les deux ne sont pas liés.

A mon avis, le rafraichissement d'écran non plus, même si on pourrait penser que cette optimisation a été pensée par les concepteurs d'Excel (j'en doute, ceci dit).

Intéressant, je vais regarder comment désactiver ces éléments de suite
Merci pour ce conseil

Invité · 24/01/2018, 14h30

si ton code est exécute à partir d'Access pourquoi pas faire une requête dans Access!

https://mon-partage.fr/f/LoCWKVjq/

**cha_choux** · 24/01/2018, 14h36

Envoyé par dysorthographie

si ton code est exécute à partir d'Access pourquoi pas faire une requête dans Access!

https://mon-partage.fr/f/LoCWKVjq/

Je trouvais plus simple d'effectuer toutes les manipulations nécessaires avant d'insérer le code dans Access

Cela me permet d'avoir une table avec seulement les deux colonnes qui m'intéresse et de ne pas polluer le fichier déjà très lourd avec des données inutiles

PS : pour ton fichier je vais essayer de l'ouvrir, mais je ne promet rien, la liaison entre Internet et le reste du poste de travail est très limitée

**Pierre Fauconnier** · 24/01/2018, 14h41

Pour ce genre de traitements, tu as toujours intérêt à passer par des formules Excel que tu places en VBA dans les cellules des colonnes souhaitées. C'est nettement plus rapide que de réaliser des boucles.

Dans ce traitement-ci où il s'agit de nettoyage, tu peux utiliser d'autres colonnes, puisque le but est de nettoyer ta feuille.

Voici un code d'exemple qui concatène 100000 lignes sur 3 colonnes. C'est instantané...

Code :

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

1
2
3
4
5
6
Sub Concat()
  Range("d1:d100000").Formula = "=concatenate(a1,b1,c1)"
  Range("d1:d100000").Copy
  Range("d1:d100000").PasteSpecial xlPasteValues
  Application.CutCopyMode = False
End Sub

En fait, ce qui est toujours intéressant dans ce genre de traitements complexes, c'est de connaître dès le début les tenants et les aboutissants, de manière à pouvoir t'orienter vers les meilleurs / moins mauvais choix possibles.

**cha_choux** · 24/01/2018, 14h50

Envoyé par Pierre Fauconnier

Pour ce genre de traitements, tu as toujours intérêt à passer par des formules Excel que tu places en VBA dans les cellules des colonnes souhaitées. C'est nettement plus rapide que de réaliser des boucles.

Dans ce traitement-ci où il s'agit de nettoyage, tu peux utiliser d'autres colonnes, puisque le but est de nettoyer ta feuille.

Voici un code d'exemple qui concatène 100000 lignes sur 3 colonnes. C'est instantané...

Code :

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

1
2
3
4
5
6
Sub Concat()
  Range("d1:d100000").Formula = "=concatenate(a1,b1,c1)"
  Range("d1:d100000").Copy
  Range("d1:d100000").PasteSpecial xlPasteValues
  Application.CutCopyMode = False
End Sub

raaaa mais vous êtes trop fort, je me sens toute ridicule avec mes lignes de codes à coté de vous...

Pour la concaténation, je vais regarder ton code, mais ce n'est pas facile car il doit effectuer l'action que si la cellule A1 et A2 sont identique (par exemple) d'où la boucle
Et il ne faut pas que j'oublies de regarder pour la désactivation des calculs et rafraichissement d'écran

**cha_choux** · 24/01/2018, 14h53

Envoyé par Pierre Fauconnier

En fait, ce qui est toujours intéressant dans ce genre de traitements complexes, c'est de connaître dès le début les tenants et les aboutissants, de manière à pouvoir t'orienter vers les meilleurs / moins mauvais choix possibles.

Mouarf c'est un des problèmes principaux, les utilisateurs me demandes des modifications toutes les demi journées, ce n'est pas facile de suivre tout en gardant un code optimisé.

Notamment quand ce n'est absolument pas ton métier

**Pierre Fauconnier** · 24/01/2018, 14h55

Dans le cas où une condition doit être remplie, tu peux la placer dans la formule que tu crées dans range("d1:d100000").formula.
Le plus simple pour moi étant de la créer dans Excel, puis de la regarder en VBA pour voir comment la construire/adapter.

Pense toujours Excel avant de penser VBA (et logique "programmeur"). Dans des traitements de ce genre, tu gagneras du temps à utiliser la puissance d'Excel dans des formules que d'utiliser du code itératif en VBA

Fonction Left() sur une colonne [XL-2007]

Macros et VBA Excel

Discussions similaires

Partager

Partager