Hidden Characters Showing In The Listbox

[Sharid]

I have these hidden characters showing in the listbox, which is on a userform. They do not show on the sheet. However I assume that they are there but hidden, is there a way to remove them from the sheet via vba as the sheet will not be visible to the user. Hopfully that will get rid of them from showing in in the listbox.

 

[NdNoviceHlp]

Hi sharid. Trial using Application.WorksheetFunction.Clean on the relevant cells. HTH. Dave

 

[Sharid]

Dave

Thanks for this, however the sheet will not be visible for the user, they will only see the results in the listbox, the range will vary, it will be between columns A-Z and X amount of rows. This is why I wanted it via Vba, so it could run at the end of the code

 

[Sharid]

It ok I have done it with this

Sub CleanUpData()
    Dim Ws As Worksheet
    Dim Rng As Range, Cell As Range
    Dim ArrCodes
    Dim i As Long
        
    Set Ws = ActiveSheet
    On Error Resume Next
    Set Rng = Ws.UsedRange.SpecialCells(xlConstants, xlNumbers + xlTextValues)
    If Rng Is Nothing Then
        Exit Sub
    End If
    On Error GoTo 0
    
    ArrCodes = Array(127, 129, 141, 143, 144, 157, 160)
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    For Each Cell In Rng
        'Use the CLEAN function to remove 32 non printing chracters (0 to 31)
        'Trim is for removing leading and trailing blanks
        Cell = Trim(WorksheetFunction.Clean(Cell))
        'Now remove character code 127, 129, 141, 143, 144, 157, 160
        For i = LBound(ArrCodes) To UBound(ArrCodes)
            Cell = Replace(Cell, Chr(ArrCodes(i)), "")
        Next i
    Next Cell
    
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

Source Link

 

[NdNoviceHlp]

Thanks for posting your outcome. Dave