我是 vba 宏的新手我正在嘗試創建一個代碼，該代碼從整個列中洗掉所有特殊字符并修剪 D 和 F 列。我在 D 和 F 列中有一些資料可能有一些特殊的即 (, . / &( ）等）我希望宏回圈到這些列 D 和 F 中，如果有特殊字符，只需洗掉特殊字符并修剪清理列 F 和 D..
我的以下代碼不起作用
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
請協助
uj5u.com熱心網友回復：
請測驗下一個方法。Replace不能接收多個字串作為What引數。因此，字串必須放在一個陣列中，然后替換每個陣列元素。另一個問題：“*”必須轉義，如果你只想替換這個字符。否則，所有內容都將替換為空字串：
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/419967.html
標籤：