Removing Any Non-Specified Characters from an Excel Spreadsheet using a Macro

Question

I'm trying to clean up a .CSV file in Excel by getting rid of any non-standard characters. The only characters I care about keeping are A-Z, 0-9, and a few standard punctuation marks. Any other characters, I'd like to delete.

I've gotten the following macro to delete an entire row when it finds a cell which contains any characters I haven't specified, but I'm not sure how to get it to actually delete the character itself.

Sub Replace()
Dim sCharOK As String, s As String
Dim r As Range, rc As Range
Dim j As Long

sCharOK = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789, `~!@#$%^&*()_+-=[]\{}|;':"",./<>?™®"

Set r = Worksheets("features").UsedRange.SpecialCells(xlCellTypeConstants, xlTextValues)

' loop through all the cells with text constant values and deletes the rows with characters not in sCharOK
For Each rc In r
    s = rc.Value
    For j = 1 To Len(s)
        If InStr(sCharOK, Mid(s, j, 1)) = 0 Then
            rc.EntireRow.Delete
            Exit For
        End If
    Next j
Next rc

End Sub

I assume there's a fairly simple way to adapt this code to that function, but I'm not familiar enough with VBA to really know how to go about doing that. Any insights are greatly appreciated!

Answer 1

Another way would be Range.Replace like:

Sub test()
  Dim sCharOK As String
  sCharOK = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789, `~!@#$%^&*()_+-=[]\{}|;':"",./<>?™®" & Chr(1)
  Dim i As Long
  For i = 0 To 255
    If InStr(sCharOK, Chr(i)) = 0 Then
      ActiveSheet.Cells.Replace What:=Chr(i), Replacement:="", LookAt:=xlPart, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False
    End If
  Next
End Sub

EDIT

looking at @ryguy72 answer also offers another way if only non-printable characters need to be deleted (at the question something like µ²äöüßÉõ will be deleted but this code will not) also assuming that there are no formulas:

Sub test()
  With ActiveSheet.UsedRange
    .Value = Evaluate("TRIM(CLEAN(" & .Address & "))")
  End With
End Sub

Or directly run in Immediate window this one-liner:

ActiveSheet.UsedRange.Value = Evaluate("TRIM(CLEAN(" & ActiveSheet.UsedRange.Address & "))")