IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Contribuez Discussion :

Code pour éliminer les espaces (TRIM) dans un tableau


Sujet :

Contribuez

  1. #21
    Membre régulier

    Inscrit en
    Janvier 2011
    Messages
    28
    Détails du profil
    Informations forums :
    Inscription : Janvier 2011
    Messages : 28
    Points : 75
    Points
    75
    Par défaut
    Merci, ça complétera utilement les références à utiliser quand je programme, malheureusement, cela ne répond pas à mes questionnement concernant ce cas précis.
    A bientôt
    Guy

  2. #22
    Rédacteur
    Avatar de Philippe Tulliez
    Homme Profil pro
    Formateur, développeur et consultant Excel, Access, Word et VBA
    Inscrit en
    Janvier 2010
    Messages
    12 729
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur, développeur et consultant Excel, Access, Word et VBA

    Informations forums :
    Inscription : Janvier 2010
    Messages : 12 729
    Points : 28 555
    Points
    28 555
    Billets dans le blog
    53
    Par défaut
    Bonjour,
    utiliser "Range("A1").CurrentRegion" plutôt que "UsedRange" ?
    Comme indiqué dans le lien proposé par Philippe, il y a plusieurs manières de sélectionner une plage de cellules.
    Pour ma part, j'utilise la propriété CurrentRegion qui est un peu l'équivalent d'un Ctrl+A.
    La liste commençant en A1, j'assigne à la variable objet Rng l'adresse de la plage des cellules de cette liste.
    La raison pour laquelle j'utiise cette propriété au lieu de UseRanged, c'est pour éviter de traiter un autre tableau ou liste qui serait présent sur la même feuille
    Imaginons une plage A1:E12 et une autre I1:K10 présentent dans la feuille nommée [Feuil1]
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    Sub test()
      With ThisWorkbook.Worksheets("Feuil1")
       MsgBox .Range("A1").CurrentRegion.Address ' Renverra A1:E12
       MsgBox .UsedRange.Address ' Renverra A1:K12
      End With
    End Sub
    Question subsidiaire, pourquoi utiliser une "Function" plutôt qu'une "Sub" ?
    Utiliser une procédure Function en lieu et place d'une Sub offre l'avantage de pouvoir l'employer comme une Sub ou une fonction. Voir les exemples donnés dans le Post #14
    Philippe Tulliez
    Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément. (Nicolas Boileau)
    Lorsque vous avez la réponse à votre question, n'oubliez pas de cliquer sur et si celle-ci est pertinente pensez à voter
    Mes tutoriels : Utilisation de l'assistant « Insertion de fonction », Les filtres avancés ou élaborés dans Excel
    Mon dernier billet : Utilisation de la fonction Dir en VBA pour vérifier l'existence d'un fichier

  3. #23
    Membre régulier

    Inscrit en
    Janvier 2011
    Messages
    28
    Détails du profil
    Informations forums :
    Inscription : Janvier 2011
    Messages : 28
    Points : 75
    Points
    75
    Par défaut
    Bonjour Philippe,

    Merci pour ces explications.

    Je viens de comprendre l'intérêt du "function", surtout grâce à cet exemple :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    TrimTable(ThisWorkbook.Worksheets("Test_").Range("B2:H5")).Interior.Color = vbYellow
    Ceci va m'ouvrir des perceptives quant à l'usage de fonctions retournant des objets (range en particulier).

    Donc si j'ai bien compris, la fonction ne travaille pas sur la totalité de la feuille, pour cela on fait un appel spécifique par exemple
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    TrimTable(ActiveSheet.UsedRage)
    Mon objectif étant de traiter un ensemble de cellules, et ne voyant pas l'intérêt de cette fonction si on ne souhaite traiter qu'une cellule (la fonction VBA Trim fait le boulot), je propose la modification suivante permettant le traitement de toute la feuille, implicitement le tableau auquel appartient la cellule ou explicitement d'un ensemble de cellules.
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
     
        Case TypeOf ShtRng Is Worksheet: Set Rng = ShtRng.UsedRange
        Case TypeOf ShtRng Is Range
            If ShtRng.Count = 1 Then 'Une seule cellule, on travaille sur le tableau auquel appartient la cellule
                Set Rng = ShtRng.CurrentRegion
            Else
                Set Rng = ShtRng
            End If
    En espérant que cela sera utile.

    A bientôt
    Guy
    A bientôt
    Guy

  4. #24
    Rédacteur
    Avatar de Philippe Tulliez
    Homme Profil pro
    Formateur, développeur et consultant Excel, Access, Word et VBA
    Inscrit en
    Janvier 2010
    Messages
    12 729
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur, développeur et consultant Excel, Access, Word et VBA

    Informations forums :
    Inscription : Janvier 2010
    Messages : 12 729
    Points : 28 555
    Points
    28 555
    Billets dans le blog
    53
    Par défaut
    Bonjour Guy,
    J'ai tardé un peu à répondre car la remarque ci-dessous m'avait interpellée et ensuite après avoir fait la correction, j'ai été occupé à d'autres choses.
    Mon objectif étant de traiter un ensemble de cellules, et ne voyant pas l'intérêt de cette fonction si on ne souhaite traiter qu'une cellule (la fonction VBA Trim fait le boulot)
    En effet, lorsque je passe comme argument Feuille.Range("A1") ou Feuille, c'est toujours dans le but de sélectionner la liste de données qui commence à A1 dans la feuille passée en argument. Toutes mes feuilles de données commençant à la première ligne, première colonne.
    En examinant mon code, j'ai constaté que j'avais effectivement omis de gérer ce cas là.
    C'est donc chose faite et pour faire ce que tu souhaites, il suffit de passer comme argument.
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    TrimTable ThisWorkbook.Worksheets("Test2_").UsedRange
    ou par le CodeName de la feuille
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    TrimTable shtDb_2.UsedRange
    Merci de m'avoir mis sur la piste de cet oubli.

    Code de la procédure TrimTable version 2.4
    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
    Function TrimTable(ShtRng As Object, Optional ValueOnly As Boolean = False) As Range
     ' Supprime tous les espaces d'une chaîne de caractères à l'exception des espaces entre les mots
     ' Renvoie un objet Range
     ' Author : Philippe Tulliez
     ' Date : 20/06/2014 (v1 - 12/02/2012)
     ' Version 2.4
     ' Update
     ' 17/01/2013 - 2.0
     ' 19/01/2013 - 2.2 - Ajouté test ShtRng Is Nothing
     '              2.3 - Affectation de TrimTable si ShtRng Is Nothing
     ' 20/06/2014   2.4 - Correction si ShtRng.Count = 1
     ' Arguments
     ' ShtRng - Object (WorkSheet ou Range)
     ' [ValueOnly] - Boolean si True transforme le résultat des formules en constante [d=False]
     ' Maximum 255 sélections de cellules non-contiguës
     Const ErrTitle As String = "Procédure - TrimTable":
     Dim ErrMsg As String: ErrMsg = "*** Sortie de procédure ***" & vbCrLf & vbCrLf
     Dim myTable() As Variant, wRow As Double, wColumn As Double
     Dim area As Byte, Rng As Range, myRange As Range
     Select Case True ' Test 1er argument
      Case ShtRng Is Nothing ' 19/01/13
       MsgBox ErrMsg & "Problème argument (ShtRng Non affecté)", vbCritical, ErrTitle
       Set TrimTable = ActiveCell
       Exit Function ' Sortie de procédure
      Case TypeOf ShtRng Is Worksheet: Set Rng = ShtRng.Range("A1").CurrentRegion
      Case TypeOf ShtRng Is Range ' 20/06/2014
       If ShtRng.Count = 1 Then Set Rng = ShtRng.CurrentRegion Else Set Rng = ShtRng
      Case Else
       ErrMsg = ErrMsg & "Problème argument - ShtRng " & vbCrLf
       MsgBox ErrMsg & "* Objet mal défini (WorkSheet) ou (Range)", vbCritical, ErrTitle
       Set TrimTable = ActiveCell
       Exit Function ' Sortie de procédure
     End Select
     
     For area = 1 To Rng.Areas.Count
      Set myRange = Rng.Areas(area)
      '
      Select Case myRange.Count
       Case 1 ' Une cellule
        If ValueOnly Then myRange = Trim(myRange.Value) Else myRange = Trim(myRange.Formula)
       Case Else
         If ValueOnly Then myTable() = myRange.Value Else myTable() = myRange.Formula
         For wRow = 1 To UBound(myTable, 1)
          For wColumn = 1 To UBound(myTable, 2)
           On Error Resume Next ' Si erreur renvoyée par une formule (ex #N/A)
           myTable(wRow, wColumn) = Trim(myTable(wRow, wColumn))
           On Error GoTo 0
          Next wColumn
         Next wRow
         myRange = myTable()
      End Select
     Next area
     Set TrimTable = Rng: Set Rng = Nothing: Set myRange = Nothing
    End Function
    Philippe Tulliez
    Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément. (Nicolas Boileau)
    Lorsque vous avez la réponse à votre question, n'oubliez pas de cliquer sur et si celle-ci est pertinente pensez à voter
    Mes tutoriels : Utilisation de l'assistant « Insertion de fonction », Les filtres avancés ou élaborés dans Excel
    Mon dernier billet : Utilisation de la fonction Dir en VBA pour vérifier l'existence d'un fichier

  5. #25
    Membre régulier

    Inscrit en
    Janvier 2011
    Messages
    28
    Détails du profil
    Informations forums :
    Inscription : Janvier 2011
    Messages : 28
    Points : 75
    Points
    75
    Par défaut Cas d'une feuille filtrée
    Bonjour Philippe,

    J'ai eu un désagrément avec une feuille dont les données étaient filtrées j'ai donc ajouté, avant la ligne 35, ce test qui interdit l'application de TrimTable si une des plages appartient à une feuille dont les données sont filtré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
     
    DIM FeuilleCourante  As Worksheet 'A mettre avec les autres déclarations en début de fonction
    'VGM 26/8/2014
    'Contrôle qu'une des plages n'est pas dans une feuille filtrée
    'Rng peut être une plage 3D ie concerner plusieurs feuilles, on doit contrôler chacune des plages
     For area = 1 To Rng.Areas.Count
         Set FeuilleCourante = Rng.Areas(area).Parent
            If Not FeuilleCourante.AutoFilter Is Nothing Then 'Un filtre a été créé sur la feuille'
               If FeuilleCourante.FilterMode Then
                ErrMsg = ErrMsg & "Problème dans <" & FeuilleCourante.Name & ">" & vbCrLf
                MsgBox ErrMsg & "* Les données sont filtrées, la suppression d'espaces n'est pas possible", vbCritical, ErrTitle
                Set TrimTable = ActiveCell
                Exit Function ' Sortie de procédure
               End If
            End If
     Next area
    'Fin VGM
    Je suis expéditif dans mon rejet, je pense qu'il doit être possible d'être plus sélectif : traitement différent suivant que les données sont filtrées ou non.

    En espérant que cela sera utile.

    A bientôt
    Guy
    A bientôt
    Guy

  6. #26
    Rédacteur
    Avatar de Philippe Tulliez
    Homme Profil pro
    Formateur, développeur et consultant Excel, Access, Word et VBA
    Inscrit en
    Janvier 2010
    Messages
    12 729
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur, développeur et consultant Excel, Access, Word et VBA

    Informations forums :
    Inscription : Janvier 2010
    Messages : 12 729
    Points : 28 555
    Points
    28 555
    Billets dans le blog
    53
    Par défaut
    Bonjour Guy,
    Merci pour ton retour, effectivement, c'est une catastrophe et j'avoue que je n'avais pas effectué de tests avec une plage filtrée.
    Je m'attelle à revoir la procédure pour intégrer la sortie de celle-ci ou éventuellement trouver une parade.
    Philippe Tulliez
    Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément. (Nicolas Boileau)
    Lorsque vous avez la réponse à votre question, n'oubliez pas de cliquer sur et si celle-ci est pertinente pensez à voter
    Mes tutoriels : Utilisation de l'assistant « Insertion de fonction », Les filtres avancés ou élaborés dans Excel
    Mon dernier billet : Utilisation de la fonction Dir en VBA pour vérifier l'existence d'un fichier

  7. #27
    Membre régulier

    Inscrit en
    Janvier 2011
    Messages
    28
    Détails du profil
    Informations forums :
    Inscription : Janvier 2011
    Messages : 28
    Points : 75
    Points
    75
    Par défaut
    Bonjour Phillipe,

    Après plusieurs mois d'utilisation, je viens de détecter 2 problèmes avec cette procédure :

    - s'il y a un tableau croisé dynamique, il arrive qu'il y ait des erreurs d'exécution (problème de valeur nulle), pour le moment, j’interdis l'exécution si la feuille contient un TCD, il faudrait être plus fin en évaluant l'intersection (Application.Intersect) entre le tableau croisé dynamique (PivotTable.TableRange2) et la zone à laquelle est appliquée le TRIM. J'ai donc ajouté ce code dans la boucle testant la présence de filtres

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
            'Pas de TRIM quand la feuille contient un tableau croisé dynamique
            'Il faudrait être plus fin en évaluant dans la boucle suivante l'intersection (Application.Intersect) entre les TDC (PivotTable.TableRange2) et Rng.Areas(area)
            If FeuilleCourante.PivotTables.Count <> 0 Then
                ErrMsg = ErrMsg & "Problème dans <" & FeuilleCourante.Name & ">" & vbCrLf
                MsgBox ErrMsg & "* Présence d'un tableau croisé dynamique, la suppression d'espaces n'est pas possible", vbCritical, ErrTitle
                Set TrimTable = ActiveCell
                Exit Function ' Sortie de procédure
            End If
    - si une cellule contient une suite de caractères numériques de type texte lors de la recopie du tableau vers la cellule ces caractères sont interprétés comme un nombre, c'est très embêtant avec des identifiants commençant pas '0' (zéro) ainsi '01' devient '1'. Je n'ai pas encore trouvé de parade.

    En espérant que cela sera utile
    Guy
    A bientôt
    Guy

  8. #28
    Rédacteur
    Avatar de Philippe Tulliez
    Homme Profil pro
    Formateur, développeur et consultant Excel, Access, Word et VBA
    Inscrit en
    Janvier 2010
    Messages
    12 729
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur, développeur et consultant Excel, Access, Word et VBA

    Informations forums :
    Inscription : Janvier 2010
    Messages : 12 729
    Points : 28 555
    Points
    28 555
    Billets dans le blog
    53
    Par défaut
    Bonjour Guy,
    Merci pour ton retour.
    J'avoue que je m'étais penché sur ton avant-dernière remarque concernant les "Cas d'une feuille filtrée" où j'ai déjà fait la modification mais pas encore publié car je voulais régler le problème des tableaux dont la détection des filtres est différente de celle d'un filtre de feuille et que nous pourrions avoir un cumul Tableau filtré + feuille avec plage filtrée.
    Depuis j'ai été occupé à d'autres tâches et j'ai perdu de vue cette modification à faire.
    Je vais terminer cela pour la fin du week-end prochain et ajouter la modification de ta dernière remarque.
    Philippe Tulliez
    Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément. (Nicolas Boileau)
    Lorsque vous avez la réponse à votre question, n'oubliez pas de cliquer sur et si celle-ci est pertinente pensez à voter
    Mes tutoriels : Utilisation de l'assistant « Insertion de fonction », Les filtres avancés ou élaborés dans Excel
    Mon dernier billet : Utilisation de la fonction Dir en VBA pour vérifier l'existence d'un fichier

  9. #29
    Membre à l'essai
    Homme Profil pro
    retraité
    Inscrit en
    Septembre 2021
    Messages
    5
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 65
    Localisation : France, Haute Savoie (Rhône Alpes)

    Informations professionnelles :
    Activité : retraité

    Informations forums :
    Inscription : Septembre 2021
    Messages : 5
    Points : 10
    Points
    10
    Par défaut Retirer les espaces début et fin
    Bonjour et un grand merci à Philippe Tulliez pour son code pour retirer les espaces début et fin de mot.
    Excellent
    @+

  10. #30
    Rédacteur
    Avatar de Philippe Tulliez
    Homme Profil pro
    Formateur, développeur et consultant Excel, Access, Word et VBA
    Inscrit en
    Janvier 2010
    Messages
    12 729
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur, développeur et consultant Excel, Access, Word et VBA

    Informations forums :
    Inscription : Janvier 2010
    Messages : 12 729
    Points : 28 555
    Points
    28 555
    Billets dans le blog
    53
    Par défaut
    Bonjour et merci,
    Cela fait toujours plaisir de recevoir un message congratulant.
    C'est très encourageant pour continuer à partager et à écrire.
    Philippe Tulliez
    Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément. (Nicolas Boileau)
    Lorsque vous avez la réponse à votre question, n'oubliez pas de cliquer sur et si celle-ci est pertinente pensez à voter
    Mes tutoriels : Utilisation de l'assistant « Insertion de fonction », Les filtres avancés ou élaborés dans Excel
    Mon dernier billet : Utilisation de la fonction Dir en VBA pour vérifier l'existence d'un fichier

Discussions similaires

  1. [AC-2010] Problème de code pour éliminer les champs vide entre 2 tables
    Par kinine dans le forum VBA Access
    Réponses: 1
    Dernier message: 15/01/2013, 21h16
  2. [XL-2007] Macro pour supprimer les espaces dans les cellules
    Par ab1to dans le forum Macros et VBA Excel
    Réponses: 7
    Dernier message: 26/11/2009, 17h25
  3. Code pour ouvrir les formulaires dans la meme fenetre
    Par maxime350 dans le forum VBA Access
    Réponses: 2
    Dernier message: 13/04/2008, 17h54
  4. Éliminer les lignes blanches dans un tableau
    Par DenPro dans le forum Balisage (X)HTML et validation W3C
    Réponses: 2
    Dernier message: 18/11/2004, 02h27

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo