Replacing Special Characters Macro

[DragonTamer]

Hello!

I know I am probably making a very basic mistake that my brain just isn't picking up on.

I am writing a simple macro to replace special characters in a column (,.?#, etc.). This has to be used in several different sheets and the columns will vary. So I have the following code started:
Sub SpecCharRepl()
'
' Special Character Replacement Macro
Dim rng As Range
Dim cell As Range
Set rng = Selection
For Each cell In rng
Do Until cell.Value = Empty
Cells.Replace What:="$", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:=",", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:=".", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Loop
Next cell
End Sub

I keep getting stuck in the loop. Please give me a good Gib smack and point out what rule I am breaking! Thanks!

 

[pgc01]

Hi
Welcome to the board

If you want to delete the characters in all the cells in the range there's no point in looping through the cells.

Try the following example:

Sub SpecCharRepl()
'
' Special Character Replacement Macro
Dim rng As Range
Dim sChars As String
Dim j As Long

Set rng = Range("A1:C10") ' range where the characters will be deleted
sChars = ",.?#" ' characters to delete

For j = 1 To Len(sChars)
    rng.Replace What:="~" & Mid(sChars, j, 1), Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, _
        MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Next j
End Sub

 

[DragonTamer]

Hi
Welcome to the board

If you want to delete the characters in all the cells in the range there's no point in looping through the cells.

Try the following example:

Sub SpecCharRepl()
'
' Special Character Replacement Macro
Dim rng As Range
Dim sChars As String
Dim j As Long

Set rng = Range("A1:C10") ' range where the characters will be deleted
sChars = ",.?#" ' characters to delete

For j = 1 To Len(sChars)
    rng.Replace What:="~" & Mid(sChars, j, 1), Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, _
        MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Next j
End Sub

Thank you for that option, I just need the range to not be tied to specific cells since we will use it on multiple sheets and the individual columns that house the data will differ. So sheet1 might be column A, while sheet2 is column K. Maybe if I combine the two codes....hmmmm