Showing posts with label VBA Utility Code for removing special character. Show all posts
Showing posts with label VBA Utility Code for removing special character. Show all posts

Friday, August 19, 2011

Remove Special Character in VBA


Some times in excel we face a problem of removing a particular character again & again. Here is VBA code which will automatically remove that special character  from that current sheet of excel.



Sub removeSplchar()
    Dim splchar, tempval, newtempval, newval As String
    Dim cell As Object
    Dim userdefrange As Range
    splchar = InputBox("Enter your spl string:")
    Set userdefrange = ActiveSheet.UsedRange
    On Error Resume Next
    For Each cell In userdefrange
    tempval = cell.Value
        For x = 1 To Len(tempval)
        newtempval = Mid(tempval, x, 1)
            If InStr(newtempval, splchar) = 0 Then
                newval = newval & newtempval
            End If
        Next x
        cell.Value = newval
        newval = ""
    Next cell
End Sub