如何使用 VBA 从整个 Excel 列中删除空格？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32013844/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to remove spaces from an entire Excel column using VBA?
提问by Alejandro Villalon
I'm trying to delete the spaces that I have in each field of the column "A" this spaces are at the end of the string some of the string has 3 spaces others 4. When I run the code, I don't have error, so I think I have a bug because nothing happened when is running.
我正在尝试删除“A”列的每个字段中的空格，这些空格位于字符串的末尾，某些字符串有 3 个空格，其他 4. 当我运行代码时，我没有错误，所以我想我有一个错误，因为在运行时什么也没发生。
Dim result As String
Last = Cells(Rows.Count, "A").End(xlUp).Row
For i = Last To 1 Step -1
If (Right(Cells(i, "A").Value, 4)) Like " " Or (Right(Cells(i, "A").Value, 3)) Like " " Then
result = Replace(Cells(i, "A"), " ", "")
End If
Next i
回答by user3561813
In your specific case, the problem is that you're storing the replacement value in a string variable named result, then doing nothing with it. If you want it to be in the Cell, you have to add it back in there, such as:
在您的特定情况下，问题在于您将替换值存储在名为 的字符串变量中result，然后什么都不做。如果您希望它在 中Cell，则必须将其添加回那里，例如：
Cells(I, "A").Value = result
Cells(I, "A").Value = result
Keep in mind, there is an Application.Trimmethod that can actually save a bit of time over looping. Experiment with code such as:
请记住，有一种Application.Trim方法实际上可以通过循环节省一些时间。尝试使用以下代码：
Dim rng as Range
set rng = Range("A1:A10")
rng.Value = Application.Trim(rng)
回答by Alex K.
Currently you are not actually updating the cell in the loop, you just;
目前，您实际上并未更新循环中的单元格，您只是；
result = Replace(Cells(i, "A"), " ", "")
You should:
你应该：
Cells(i, "A") = Replace(Cells(i, "A"), " ", "")
Or better
或更好
Cells(i, "A") = rtrim$(Cells(i, "A"))
Which will remove all right spaces. You can probably remove the if check as well.
这将删除所有正确的空格。您也可以删除 if 检查。
回答by 99moorem
What are expecting this code to do? as currently the values are being read into result. It would also be better to use trim to remove trailing spaces.
期望此代码做什么？因为目前正在将值读入结果。最好使用修剪来删除尾随空格。
Dim result As String
Last = Cells(Rows.Count, "A").End(xlUp).Row
For i = Last To 1 Step -1
result = RTrim(Cells(i, "A"))
Next i
