Calyx Teren
asked on
Edit macro to remove all special characters from a single column
I'm trying to edit a macro so that it removes all special characters, except the ones listed, within a specified column instead of globally. I think this is the only line that needs editing:
objCell.Value = RegEx.Replace(objCell.Valu e, "")
Sub RegExReplace()
Dim RegEx As Object
Set RegEx = CreateObject("VBScript.Reg Exp")
RegEx.Global = True
RegEx.Pattern = "[^A-Za-z0-9_-() ]"
For i = 2 To Range("B1048576").End(xlUp ).Row
objCell.Value = RegEx.Replace(objCell.Valu e, "")
Next
End Sub
objCell.Value = RegEx.Replace(objCell.Valu
Sub RegExReplace()
Dim RegEx As Object
Set RegEx = CreateObject("VBScript.Reg
RegEx.Global = True
RegEx.Pattern = "[^A-Za-z0-9_-() ]"
For i = 2 To Range("B1048576").End(xlUp
objCell.Value = RegEx.Replace(objCell.Valu
Next
End Sub
ASKER
I got an application-define or object-defined error, error 5021.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
objCell.Value = RegEx.Replace(objCell.Valu
...with this...
Cells(i, "B").Value = RegEx.Replace(Cells(i, "B").Value, "")