VBA Macro that removes special characters from column D and F-CodePudding

I am new to vba macro I am trying to create a code that removes all specials characters form entire column and trim column D and F. I have some data in column D and F which might have some special viz (, . / &() etc) I want the macro to loop into these columns D and F and just remove the special characters if any and trim clean the column F and D..

My Codes below which doesn't work

Sub Splchrdel()

Dim ws As Worksheet
Dim Rng As Range


    Set ws = ActiveWorkbook.Worksheets("Final Exclusion")

    Set Rng = ws.Range("D2:D", "F2:F")
    
With ws

    Rng.Replace What:=(("&", ",", ".", "/", "-", " ", "#", "!", "%", "(", ")", "*", _
"'", "", "$")) Replacement:="", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    
 Rng("D2:D:", "F2:F").Trim.Clean
 
    
End Sub

Kindly assist

CodePudding user response：

Please, test the next way. Replace cannot receive multiple strings as What parameter. So, the strings must be placed in an array and then replace each array element. Another issue: "*" must be escaped, if you want to replace only this character. Otherwise, everything will be replaced with a null string:

Sub Splchrdel()
 Dim ws As Worksheet, Rng As Range, lastR As Long, arrWhat, El

    Set ws = ActiveWorkbook.Worksheets("Final Exclusion")
    lastR = ws.Range("D" & ws.Rows.count).End(xlUp).row 'if F:F has different number of rows, another lastR should be calculated
    Set Rng = ws.Range("D2:D" & lastR & "," & "F2:F" & lastR) 'range only for D:D and F:F columns
   
    arrWhat = Array("&", ",", ".", "/", "-", " ", "#", "!", "%", "(", ")", "~*", "'", "", "$")
    For Each El In arrWhat
        Rng.Replace What:=El, replacement:="", LookAt:=xlPart, _
                   SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                    ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    Next El
    'Since `Clean` does not work on a range, an iteration is needed:
    Dim cel As Range
    For Each cel In Rng.cells
        cel.Value = WorksheetFunction.Clean(Trim(cel.Value))
    Next
End Sub