CODEWITHSUNDEEP
excelvb.net
Elif Karaoglu
Elif Karaoglu

Reputation: 21

Removing unwanted characters from Excel(Visual Basic)

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

Upvotes: 2

Views: 361

Answers (2)

Gary's Student
Gary's Student

Reputation: 96753

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

Upvotes: 1

JvdV
JvdV

Reputation: 75850

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.

Upvotes: 3

Related Questions