Discussion :

[Guy_M]

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.

[Philippe Tulliez]

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

[Guy_M]

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

[Philippe Tulliez]

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

[Guy_M]

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

[Philippe Tulliez]

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.

[Guy_M]

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

[Philippe Tulliez]

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.

[Geoff1958]

Bonjour et un grand merci à Philippe Tulliez pour son code pour retirer les espaces début et fin de mot.
Excellent
@+

[Philippe Tulliez]

Bonjour et merci,
Cela fait toujours plaisir de recevoir un message congratulant.
C'est très encourageant pour continuer à partager et à écrire.