Link to home
Start Free TrialLog in
Avatar of Calyx Teren
Calyx TerenFlag for United States of America

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.Value, "")




Sub RegExReplace()

    Dim RegEx As Object
    Set RegEx = CreateObject("VBScript.RegExp")
    RegEx.Global = True

    RegEx.Pattern = "[^A-Za-z0-9_-() ]"
    For i = 2 To Range("B1048576").End(xlUp).Row
        objCell.Value = RegEx.Replace(objCell.Value, "")
    Next

End Sub
Avatar of Wayne Taylor (webtubbs)
Wayne Taylor (webtubbs)
Flag of Australia image

Replace this...

    objCell.Value = RegEx.Replace(objCell.Value, "")

...with this...

    Cells(i, "B").Value = RegEx.Replace(Cells(i, "B").Value, "")
Avatar of Calyx Teren

ASKER

I got an application-define or object-defined error, error 5021.
ASKER CERTIFIED SOLUTION
Avatar of Wayne Taylor (webtubbs)
Wayne Taylor (webtubbs)
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial