从使用VBA for Excel的大范围单元格中删除“额外”空格(多于1个)的最快方法 [英] Fastest way to remove 'extra' spaces (more than 1) from a large range of cells using VBA for excel
问题描述
我尝试过的一些方法包括:
对于每个c在范围
c.Value =修剪(c.Value)
下一个c
和
对于每个c在范围内
c = WorksheetFunction.Trim(c)
下一步c
和
对于每个c在范围
/ pre>
c.Value =替换(c.Value,,)
下一步c
任何提高速度的想法?
提前谢谢。
解决方案循环正在杀死你。这将一次删除整列中的空格:
Sub SpaceKiller()
工作表(Sheet1) .Columns(A)。替换_
什么:=,_
替换:=,_
SearchOrder:= xlByColumns,_
MatchCase:= True
End Sub
调整范围适合。如果要删除 double 空格,则:
Sub SpaceKiller()
工作表(Sheet1)。列(A)。替换_
什么:=,_
替换:=,_
SearchOrder:= xlByColumns,_
MatchCase:= True
End Sub
编辑#1:
此版本将以单打替换双打,然后检查是否还剩下两倍!
Sub SpaceKiller3()
工作表(Sheet1)。列(A)替换_
什么:=,_
替换:= ,_
SearchOrder:= xlByColumns,_
MatchCase:= True
设置r =工作表(Sheet1)。列(A)。 :=)
如果r不是,然后
MsgBoxdone
Else
MsgBox请再次运行
End If
End Sub
您可以重新运行,直到看到 完成
$基于 Don Donoghue 的评论,b $ b编辑#2:
运行递归,直到所有的双倍转换为单打:
Sub SpaceKiller3()
工作表(Sheet1)。列(A)。替换_
什么:=,_
替换:=,_
SearchOrder:= xlByColumns,_
MatchCase:= True
设置r =工作表(Sheet1)。列(A)。Find(What:=)
如果r不是,然后
MsgBoxdone
Else
Call SpaceKiller3
End If
End Sub
I've been looking around and haven't found a great answer to the question on how to remove extra spaces from a large range of cells containing text strings. Let's say 5000+ cells.
some ways I have tried include:
For Each c In range c.Value = Trim(c.Value) Next c
and
For Each c In range c = WorksheetFunction.Trim(c) Next c
and
For Each c In range c.Value = Replace(c.Value, " ", " ") Next c
any ideas for speed improvement?
thank you in advance.
解决方案The loop is killing you. This will remove spaces in an entire column in one shot:
Sub SpaceKiller() Worksheets("Sheet1").Columns("A").Replace _ What:=" ", _ Replacement:="", _ SearchOrder:=xlByColumns, _ MatchCase:=True End Sub
Adjust the range to suit. If you want to remove double spaces, then:
Sub SpaceKiller() Worksheets("Sheet1").Columns("A").Replace _ What:=" ", _ Replacement:=" ", _ SearchOrder:=xlByColumns, _ MatchCase:=True End Sub
EDIT#1:
This version will replace doubles with singles and then check if there are still still doubles left!
Sub SpaceKiller3() Worksheets("Sheet1").Columns("A").Replace _ What:=" ", _ Replacement:=" ", _ SearchOrder:=xlByColumns, _ MatchCase:=True Set r = Worksheets("Sheet1").Columns("A").Find(What:=" ") If r Is Nothing Then MsgBox "done" Else MsgBox "please run again" End If End Sub
You can re-run until you see done
EDIT#2:
based on Don Donoghue's comment, this version will run recursively until all double are converted to singles:
Sub SpaceKiller3() Worksheets("Sheet1").Columns("A").Replace _ What:=" ", _ Replacement:=" ", _ SearchOrder:=xlByColumns, _ MatchCase:=True Set r = Worksheets("Sheet1").Columns("A").Find(What:=" ") If r Is Nothing Then MsgBox "done" Else Call SpaceKiller3 End If End Sub
这篇关于从使用VBA for Excel的大范围单元格中删除“额外”空格(多于1个)的最快方法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!