DragonTamer

New Member
Joined
Jun 13, 2018
Messages
3
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!
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,823
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:

Code:
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

New Member
Joined
Jun 13, 2018
Messages
3
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:

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,621
Messages
5,523,947
Members
409,543
Latest member
LaMaqu1na

This Week's Hot Topics

Top