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

**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

**cha_choux** · 24/01/2018, 15h06

Envoyé par Pierre Fauconnier

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.

J'ai testé :

Code :

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

1
2
3
Application.ScreenUpdating = False
 
 Application.ScreenUpdating = True

4 secondes de gagnées, merci !
Je vais l'ajouter à chaque manipulation d'Excel de cette grosse MAJ quotidienne.

Pour :

Code :

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

1
2
3
 
Application.Calculation = xlCalculationManual
Application.Calculation = xlCalculationAutomatic

Celui-ci ne me fait pas gagner de temps, ce qui me semble logique puisque l'Excel est uniquement le résultat d'un export d'un logiciel.

Je prend en tout cas bien note de ces éléments super utiles

**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

**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

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!

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, 15h29

Si c'est un extract d'un logiciel, je suppose qu'il est constant (au moins dans la forme et la structure).

Je réalise souvent des automatisations de nettoyage d'extract SAP et en général, c'est assez simple à nettoyer, et surtout, constant d'une fois à l'autre (pour une même demande SAP, bien entendu).

En créant des petites fonctions, tu simplifies ta maintenance en cas de modifs dans la forme de l'extract

**cha_choux** · 24/01/2018, 15h43

Envoyé par Pierre Fauconnier

Si c'est un extract d'un logiciel, je suppose qu'il est constant (au moins dans la forme et la structure).

Je réalise souvent des automatisations de nettoyage d'extract SAP et en général, c'est assez simple à nettoyer, et surtout, constant d'une fois à l'autre (pour une même demande SAP, bien entendu).

En créant des petites fonctions, tu simplifies ta maintenance en cas de modifs dans la forme de l'extract

J'essaye

, c'est pour cela également que je blinde le code de commentaire.

Je regarderais à l'occasion pour passer par le nom des colonnes que je conserve, plutôt que de compter combien de colonnes je dois supprimer, j'aurais moins de chance d'avoir un bug s'ils décident de modifier la requête d'extraction

Mais pour l'instant place au formula !

**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

Vue hybride

Discussions similaires

Partager

Partager