如何使用VBA从整个Excel列中删除空格?

时间:2023-01-01 22:13:13

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

3 个解决方案

#1


7  

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检查。

#2


6  

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

单元格(I,“A”)。值=结果

Keep in mind, there is an Application.Trim method 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)

#3


0  

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.

期待这段代码的作用是什么?目前正在将值读入结果。使用trim来删除尾随空格也会更好。

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

#1


7  

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检查。

#2


6  

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

单元格(I,“A”)。值=结果

Keep in mind, there is an Application.Trim method 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)

#3


0  

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.

期待这段代码的作用是什么?目前正在将值读入结果。使用trim来删除尾随空格也会更好。

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