|
老朽再次斗胆,增加了左右和中间选项,共5 种 。经测试楼上的附件,结果正确!
Sub 批量剔除空格() '批量剔除空格,可以是区域,也可以是单列或者多列
Application.ScreenUpdating = False
Dim I As Long , J As Long , Arr, T As Single , Ans
Ans = Application.InputBox("请选择剔除全部、左边还是右边、中间还是两边?" & vbLf & vbLf _
& "1:剔除全部空格。 " _
& "2:剔除左边空格。" & vbLf _
& "3:剔除右边空格。 " _
& "4:同时剔除两边空格。" & vbLf _
& "5:剔除中间空格,保留两端空格。", "剔除方式", 1, 100, 100, , , 1)
T = Timer
Select Case Ans
Case 1 '剔除全部空格
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart
Case 2 '剔除左边空格
Arr = Selection
For I = 1 To UBound (Arr, 1)
For J = 1 To UBound (Arr, 2)
Arr(I, J) = LTrim(Arr(I, J))
Next
Next
Selection = Arr
Case 3 '剔除右边空格
Arr = Selection
For I = 1 To UBound (Arr, 1)
For J = 1 To UBound (Arr, 2)
Arr(I, J) = RTrim(Arr(I, J))
Next
Next
Selection = Arr
Case 4 '同时剔除两边空格。
Arr = Selection
For I = 1 To UBound (Arr, 1)
For J = 1 To UBound (Arr, 2)
Arr(I, J) = Trim(Arr(I, J))
Next
Next
Selection = Arr
Case 5 '剔除中间空格,保留两端空格
Arr = Selection
For I = 1 To UBound (Arr, 1)
For J = 1 To UBound (Arr, 2)
Arr(I, J) = Replace(Arr(I, J), Trim(Arr(I, J)), Replace(Trim(Arr(I, J)), " ", ""))
Next
Next
Selection = Arr
End Select
Application.ScreenUpdating = True
MsgBox "替换完毕" & vbLf & "用时共计 " & Timer - T & " 秒!", 64 + vbOKOnly, "友情提示"
End Sub
|
|