VBA Macro that removes special characters from column D and F
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
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