Hidden Characters Showing In The Listbox

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,047
Office Version
  1. 2016
Platform
  1. Windows
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.


1613395056962.png
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,766
Hi sharid. Trial using Application.WorksheetFunction.Clean on the relevant cells. HTH. Dave
 

Sharid

Well-known Member
Joined
Apr 22, 2007
Messages
1,047
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Apr 22, 2007
Messages
1,047
Office Version
  1. 2016
Platform
  1. Windows
It ok I have done it with this

VBA Code:
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
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,127,298
Messages
5,623,843
Members
415,995
Latest member
SergioCM92

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top