Removing unwanted characters from Excel(Visual Basic)

Question

I have an excel file. I want to check cell values. If cell include unwanted char (such as newline, VbCrLf ,vbLf, vbCr) i want to remove this char from cell and then save excel.

How can i achieve this with visual basic?I don'T know visual basic. I couldn't convert cell value to string. This code didn't work:

Dim wb, ws As Object
Dim excel, sheet, range As Object
Dim Success
Dim oneCell As Object

Try

wb = GetWorkbook(Handle, Workbook)
ws = GetWorksheet(Handle, Workbook, Worksheet)

wb.Activate()
ws.Activate()
excel = ws.Application
sheet = excel.ActiveSheet

ws.UsedRange.Select()

For Each oneCell In excel.Selection
oneCell.Value = excel.Substitute(excel.Substitute(CStr(oneCell.Value),vbLf, vbCr), vbCr, "-").Trim()
oneCell.Value = excel.WorksheetFunction.Clean(oneCell)
Next oneCell

Success = True

Catch e As Exception
    Success = False
  ''  Message = e.Message
Finally
    wb = Nothing
    ws = Nothing
    excel = Nothing
    sheet = Nothing
    range = Nothing
End Try

You can try [this](https://stackoverflow.com/questions/24356993/removing-special-characters-vba-excel/24357636) or [this](https://stackoverflow.com/questions/30024421/excel-vba-remove-part-of-the-string) [this](https://stackoverflow.com/questions/31188839/vba-function-to-remove-all-special-characters) would work too. — Warcupine, Jun 30 '20 at 13:17
I get this error: Unable to get the Clean property of the WorksheetFunction class. — Elif Karaoglu, Jul 02 '20 at 12:17

score 3 · Answer 1 · answered Jun 30 '20 at 13:24

3

Try to call Application.Clean on your Range object. A very basic example is to use it like:

Range("A1").Value = Application.Clean([A1])

However, you can call this on a larger range. Application.Clean will remove all non-printable characters.

answered Jun 30 '20 at 13:24

JvdV

70,606
8
39
70

score 1 · Answer 2 · answered Jun 30 '20 at 13:24

1

Here is an example of removing ASCII-10 and ASCII-13 from the cells in a worksheet:

Sub KleanUp()
    With Cells
        .Replace what:=Chr(10), replacement:=""
        .Replace what:=Chr(13), replacement:=""
    End With
End Sub

answered Jun 30 '20 at 13:24

Gary's Student

95,722
10
59
99

Removing unwanted characters from Excel(Visual Basic)

2 Answers2