VBA – Macro to Clean Non-printable characters

Sometimes, your data contains some characters which make some portion of your data unusable. Below is a macro which cleans your worksheet in the following way –

1. It will remove non-printable characters with ASCII codes 0 to 31.
2. It will remove leading and trailing blanks.
3. Will remove characters with ASCII codes 127, 129, 141, 143, 144, 157, 160.

A good introduction to above character codes is at https://www.thoughtco.com/remove-non-printable-characters-3123798

The workbook containing the macro can be downloaded from Clean up Data Macro

1. Make a backup of your workbook.
2. Open your workbook and ALT+F11
3. Locate your Workbook name in Project Explorer Window
4. Right click on your workbook name > Insert > Module
5. Copy paste the Macro code given
6. Go back to your Workbook and ALT+F8 to display Macro Window
7. Run your Macro from here
8. Delete you Macro if the Macro was needed to be run only once.
9. Otherwise save your file as .xlsm if you intend to reuse Macro again.

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