Consolidating VB code (Removing 29 different Special Characters)

I made this VB code today - but problem is it's SO HUGE. Can someone possibly look at it and shorten it some?

It basically just looks at a given range, and removes ALL special Characters - and then gives a break down as to how many it removed.

This code DOES WORK AS IS - I just want to shorten it's length - not alter what it does.

- Also, does anyone know of a better way to display the breakdown of characters removed? It's a little sloppy and the msgbox character limit maxed out.


EDIT: I just tried to copy in the code and I maxed out this thead, haha. I attached a copy of the workbook.

Hi Adgjqetuo,

The code to replace all of the special characters you've specified can be shortened to:

Sub test()
Dim myString As String, ce As Range, i As Long
Application.ScreenUpdating = False
For Each ce In Range("A1:V500")
    For i = Len(ce.Value) To 1 Step -1
        Select Case Mid(ce.Value, i, 1)
            Case Is = "`", "!", "@", "#", "$", "%", "^", "&", "(", ")", "_", "-", "=", "+", _
                "{", "[", "}", "]", "\", "|", ";", ":", "'", """", ",", "<", ".", ">", "/"
                    myString = Replace(ce.Value, Mid(ce.Value, i, 1), "")
                    ce.Value = myString
            Case Else
                    ' Do Nothing
        End Select
    Next i
    myString = ""
Next ce
Application.ScreenUpdating = True
End Sub

This doesn't prompt the user whether they want to do the replacements, or show a results window with the count of each special character replaced. Is the latter really important? (Do the users care that 18 . and 4 / were removed?) If you absolutely need those features, they can be (somehow) added to this code.

PS - Try this code on a backup copy of your workbook. Don't want you deleting something important.