Microsoft 365 Suppression de caractères non visibles

Sub a()
    [A1].Value = "abc" & Chr(8) & "def" & Chr(20) & "ghi"
End Sub

Sub SupprimeCaracteresDeContrôle()
    Dim Rng As Range
    Dim Cel As Range
    Dim vn As Integer
    Dim vs As String
    Dim i As Integer
    Dim k As Integer
    Dim Change As Boolean
 
    Set Rng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants, xlTextValues)
    'MsgBox Rng.Address
 
    Application.ScreenUpdating = False
 
    For Each Cel In Rng
        vs = Cel.Value
        i = 1
        k = Len(vs)
        Change = False
        Do While i <= k
            vn = Asc(Mid(vs, i, 1))
            If vn < 32 And vn <> 10 Then
                'MsgBox vn
                vs = Replace(vs, Chr(vn), "")
                k = Len(vs)
                Change = True
            Else
                i = i + 1
            End If
        Loop
        If Change Then Cel.Value = vs
    Next Cel
 
    Application.ScreenUpdating = True
End Sub

'-------------------------------------------
'Retire les caractères de contrôle (sauf LF)
'de la feuille active
'-------------------------------------------
Sub SupprimeCaracteresDeContrôle()
    Dim Rng As Range
    Dim Area As Range
    Dim TabCells As Variant
    Dim i As Integer
    Dim j As Integer
    Dim Change As Boolean
    
    Set Rng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants, xlTextValues)
    If Rng Is Nothing Then Exit Sub
    'MsgBox Rng.Address
    
    Application.ScreenUpdating = False
    
    For Each Area In Rng.Areas
        If Area.Cells.Count = 1 Then
            ReDim TabCells(1 To 1, 1 To 1)
            TabCells(1, 1) = Area.Value
        Else
            TabCells = Area.Value
        End If
        Change = False
        
        For i = 1 To UBound(TabCells, 1)
            For j = 1 To UBound(TabCells, 2)
                Change = Change Or RemoveControlChars(TabCells(i, j))
            Next j
        Next i
        
        'Si modification dans l'Area, valoriser ses cellules
        If Change Then Area.Value = TabCells
    Next Area
    
    Application.ScreenUpdating = True
End Sub

'---------------------------------------------
'Retire les caractères de contrôle (sauf LF)
'de la chaine de caractère passée en paramètre
'---------------------------------------------
Private Function RemoveControlChars(Valeur As Variant) As Boolean
    Dim AscChar As Integer
    Dim i As Integer
    Dim Nb As Integer
    Dim Change As Boolean
    
    'Init Return Value
    RemoveControlChars = False
    
    If VarType(Valeur) <> vbString Then Exit Function
    
    i = 1
    Nb = Len(Valeur)
    Change = False
    
    Do While i <= Nb
        AscChar = Asc(Mid(Valeur, i, 1))
        If AscChar < 32 And AscChar <> 10 Then
            Valeur = Replace(Valeur, Chr(AscChar), "")
            Nb = Len(Valeur)
            Change = True
        Else
            i = i + 1
        End If
    Loop
    
    'Return Value
    RemoveControlChars = Change
End Function

Dim car, i As Long
car = Array(8, 10, 13, 160)
Set pl = ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants, xlTextValues)
For i = 0 To UBound(car)
    pl.Replace Chr(car(i)), " "
Next

Dim car, i As Long
car = Array(8, 10, 13, 160)
Set pl = ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants, xlTextValues)
For i = 0 To UBound(car)
    pl.Replace Chr(car(i)), " "
Next

'Il manque une fonctionnalité existante dans la version initiale qui supprimait les espaces en double, triple...
Set toto = pl.Find("  ", LookIn:=xlValues)
While Not (toto Is Nothing)
    pl.Replace "  ", " "
    Set toto = pl.Find("  ", LookIn:=xlValues)
Wend

If AscChar < 32 And AscChar <> 10 Then

If (AscChar < 32 And AscChar <> 10) or AscChar = 160 Then