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!
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top