XL 2019 Suppression espace de début et fin - Macro très rapide

Bastien43 · 28 Juillet 2021

Bonjour,

J'ai créé cette macro pour supprimer les espaces de début et de fin de chaque cellule (de toute une colonne : 12000 lignes)

Comment accélérer la macro, ce n'est pas rapide... ?? Existe-t-il un code plus rapide ?

VB:

Sub SupEspace()

Dim plage, cellule

Set plage = Range("D2:D12000")

For Each cellule In plage

    cellule.Value = Trim(cellule.Value)

Next cellule

End Sub

Efgé · 28 Juillet 2021

Bonjour Bastien43

Par tableau (array)

VB:

Sub SupEspace2()

Dim plage As Range
Dim T As Variant
Dim i&, j&

Set plage = Range("D2:D12000")
T = plage
For i = LBound(T, 1) To UBound(T, 1)
    For j = LBound(T, 2) To UBound(T, 2)
        T(i, j) = Trim(T(i, j))
    Next j
Next i
plage.FormulaLocal = T
End Sub

Cordialement

job75 · 30 Juillet 2021

Merci beaucoup Efgé, donc sur 2007 ça ne va pas.

Il faudrait savoir ce qui se passe sur 2003, 2010, 2013, 2016.

Edit : patricktoulon a testé sur 2013, ça ne va pas.

job75 · 30 Juillet 2021

Efgé (2007) et patricktoulon est-ce que ça fonctionne en remplaçant D2: D12000 par D2: D100 ?

patricktoulon · 30 Juillet 2021

re
sur 2013 32 bit ça fait pareil que 2007

on l'avait déjà vu ça avec @Yeahou
sur 2016 et 365 les formules sont automatiquement converties en matricielle
la mienne créé la matricielle et est compatible toute version
et en plus sur 365 y a même plus les accolades pour nous signaler que c'est des matricielles
sur 2016 je sais pas
ça c'est une information qu'il faudrait faire remonter a MS car c'est gênant
si on veut pas faire des matricielles
sinon il va y avoir de sérieux problèmes de compatibilité entre les versions

Efgé · 30 Juillet 2021

Re

Efgé (2007) est-ce que ça fonctionne en remplaçant D2: D12000 par D2: D100 ?

Non

patricktoulon · 30 Juillet 2021

re
avec D2100 c'est pareil

Bernard_XLD · 30 Juillet 2021

Bonjour à tous

Testé sous 365, résultat correct avec la macro de Job75
J'ai ajouté une intersection implicite pour désactiver la matricielle dynamique de 365 et là, je reproduis le même problème que Patrick sous 2013 et Efgé sous 2007.

Cordialement, @+

Bernard_XLD · 30 Juillet 2021

@patricktoulon et @Efgé ,

par curiosité, vous pouvez essayer avec cette syntaxe ?

Bien cordialement, @+

VB:

Sub SupEspace()
With [D2:D100]
    .Name = "P" 'plage nommée
    .Value = Evaluate("=Trim(P)") 'SUPPRESPACE
End With
End Sub

patricktoulon · 30 Juillet 2021

re
non @Yeahou ça ne marchera pas
pour les version antérieures a 2016 il faut reconstruire la matricielle
Bon j'ai testé quand même par curiosité
ça c'est un gros problème je vous dis qu'il va falloir résoudre car c'est pas la première fois que l'on voit une bizarrerie pareille

Efgé · 30 Juillet 2021

Yeahou à dit:
@patricktoulon et @Efgé ,

par curiosité, vous pouvez essayer avec cette syntaxe ?

Bien cordialement, @+

VB:

Sub SupEspace() With [D2:D100] .Name = "P" 'plage nommée .Value = Evaluate("=Trim(P)") 'SUPPRESPACE End With End Sub

Même résultat sous 2007: Répétition de la première ligne sur l'ensemble de la plage

Cordialement

job75 · 30 Juillet 2021

patricktoulon à dit:
sinon il va y avoir de sérieux problèmes de compatibilité entre les versions

Bah ce ne sera pas la 1ère fois que la compatibilité descendante n'est pas respectée.

Puisque ma macro du post #8 ne va pas utilisez celle-ci qui fonctionne sur toute version :

VB:

Sub SupEspace()
Application.ScreenUpdating = False
With [D2:D12000]
    .EntireColumn.Insert 'insère une colonne auxiliaire
    .Columns(0) = "=TRIM(RC[1])" 'SUPPRESPACE
    .Columns(0) = .Columns(0).Value 'supprime les formules
    .Value = .Columns(0).Value
    .Columns(0).EntireColumn.Delete
End With
End Sub

patricktoulon · 30 Juillet 2021

a oui c'est une idée mais il y a
(deux calculate * le nombre de cells * le nombre de cells liées a celles ci par formule )de déclenchés
sur un range important ça va se sentir
avec la mienne il n'y en a qu'un(* le nombre de cellules liées à celles ci ) (normal puisque l'on change simplement la valeur dans les cellules elles mêmes

VB:

Function TriMAllCellsInRange(ByRef RnG As Range)'trim le left et right
'supprime les espace en debut et fin de chaine de  caracteres dans une plage  equivalent de "Ltrim" in one shoot
    With RnG.Parent.Range(RnG.Address)
        .Value = Evaluate("IF(ISTEXT(" & .Address & "),MID(" & .Address & ",FIND(MID(TRIM(" & .Address & "),1,2)," & .Address & ",1),LEN(" & .Address & ")),REPT(" & .Address & ",1))")
        TriMAllCellsInRange = Evaluate("IF(ISTEXT(" & .Address & "),MID(" & .Address & ",1,FIND(TRIM(RIGHT(SUBSTITUTE(TRIM(" & .Address & "), "" "", REPT("" "", 100)), 100))," & .Address & ",1)+LEN(TRIM(RIGHT(SUBSTITUTE(TRIM(" & .Address & "), "" "", REPT("" "", 100)), 100)))-1),REPT(" & .Address & ",1))")
    End With
End Function
'
'
'equivalent à application.trim de VBA(régularSpace)
Function SupprfirstAndNexAndDoubleSpaceInRange(ByRef RnG As Range)
'supprime tout les espaces avant et apres la chaine et tout les doubles espaces dans la chaine in one shoot
    With RnG
        SupprfirstAndNexAndDoubleSpaceInRange = Evaluate("IF(ISTEXT(" & .Address & "),TRIM(" & .Address & "),REPT(" & .Address & ",1))")
    End With
End Function '

Sub test() 'trim les valeurs dans la plage
    Dim DL, RnG As Range
    DL = Cells(Rows.Count, 3).End(xlUp).Row
    Set RnG = Sheets(1).Range("C2:C" & DL)
    RnG.Value = TriMAllCellsInRange(RnG)
End Sub

Sub test2() 'trim et supprime les espaces consécutif des valeurs
    Dim DL, RnG As Range
    DL = Cells(Rows.Count, 3).End(xlUp).Row
    Set RnG = Sheets(1).Range("C2:C" & DL)
    RnG.Value = SupprfirstAndNexAndDoubleSpaceInRange(RnG)
End Sub

c'est le rept qui recrée la matrice ,evaluate me renvoie alors une matrice et non un string comme dans ton post 8

job75 · 30 Juillet 2021

J'ai testé sur 1 000 000 de lignes avec la plage C2:C1000000 remplie par =" "&LIGNE()&" ":

- macro du post #3 => 0,95 seconde

- macro du post #25 => 3,3 secondes

- macro test du post #26 => 10,4 secondes

- macro test2 du post #26 => 2,4 secondes.

patricktoulon · 30 Juillet 2021

re
ma fois la disparité de fonctionnement entre version d'excel commence a devenir déconcertante

patricktoulon · 30 Juillet 2021

re
et c'est même pire chez moi le post 3 ne fonctionne pas
je raconte pas d’histoire vous l'avez sous les yeux

Efgé · 30 Juillet 2021

patricktoulon à dit:
re
et c'est même pire chez moi le post 3 ne fonctionne pas
je raconte pas d’histoire vous l'avez sous les yeux
Regarde la pièce jointe 1112280

La question portait sur les espaces Avant et Après le texte. Il n'a été question de supprimer les doubles espaces à l'intérieur des textes que dans tes interventions.

Cordialement

XL 2019 Suppression espace de début et fin - Macro très rapide

Bastien43

XLDnaute Occasionnel

Efgé

job75

XLDnaute Barbatruc

job75

XLDnaute Barbatruc

patricktoulon

XLDnaute Barbatruc

Efgé

XLDnaute Barbatruc

patricktoulon

XLDnaute Barbatruc

Bernard_XLD

XLDnaute Barbatruc

Bernard_XLD

XLDnaute Barbatruc

patricktoulon

XLDnaute Barbatruc

Efgé

XLDnaute Barbatruc

job75

XLDnaute Barbatruc

Pièces jointes

patricktoulon

XLDnaute Barbatruc

job75

XLDnaute Barbatruc

patricktoulon

XLDnaute Barbatruc

patricktoulon

XLDnaute Barbatruc

Efgé

XLDnaute Barbatruc

Discussions similaires