How to apply CLEAN() formula to entire Excel sheet?

Question

I have an Excel file with almost 50 columns and 10,000 rows. These contain non-printable characters in some cells; I want to remove these characters from all cells in the sheet.

It would be difficult to apply the CLEAN() function to each and every column. Is there an alternative?

Answer 1

I can't see it visually.. If you see that character at the end of the cell text, then you can find that value using the below code

debug.Print asc(right(range("D1").Value,1))

Simply replace 110 with that value in the code below.

Sub Sample()
    Dim ws As Worksheet

    '~~> Set this to the relevant worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    ws.Cells.Replace What:=Chr(110), Replacement:="", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

HERE is the ASCII table for your reference.

Answer 2

To apply the excel CLEAN function on each cell of the first 50 columns and 10000 rows:

Sub cleanSheet()
    Dim r As Range
    Application.ScreenUpdating = False
    For Each r In Range(Cells(1, 1), Cells(10000, 50))
        r = WorksheetFunction.Clean(r)
    Next r
    Application.ScreenUpdating = True
End Sub