How to remove special characters from columns D and F?

Question

I have data in column D and F which might have special characters (, . / + &() etc.).
I want to remove the special characters if any and trim clean the columns.

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

`"D2:D"` and `"F2:F"` are not valid range references. They either need an ending row number, or to have the `2` removed. Also you probably need a loop. — BigBen, Jan 22 '22 at 16:40
Then how do I range it I want entire column D starting from D2 cell till end excel column D and entire column F starting from F2 cell till end excel column F — Sandy Candy, Jan 22 '22 at 16:43
[Find the last cell](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba). Or if you have a table (`ListObject`), use `.ListColumns("DF").DataBodyRange`. — BigBen, Jan 22 '22 at 16:45
I am not getting this please can u help it in codes I need to remove the special chars as mentioned in the question — Sandy Candy, Jan 22 '22 at 17:00
yes thats why I want to code from range D2 and F2 till end of column D and F — Sandy Candy, Jan 22 '22 at 17:08

FaneDuru · Accepted Answer · 2022-01-22T17:55:13.113

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

How to remove special characters from columns D and F?

1 Answers1