在 Excel 中使用 VBA 修剪单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2964769/
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
Trim Cells using VBA in Excel
提问by Greg Reynolds
I have what seems like a simple problem with some data in Excel. I have a lot of data with leading spaces pasted from a web table, and I would like to get rid of the initial space. I cribbed the following code (I am completely new to VBA), but it doesn't seem to work. When I step through it in the debugger it looks like an infinite loop. Any help would be appreciated!
我对 Excel 中的某些数据似乎有一个简单的问题。我有很多从网络表粘贴前导空格的数据，我想摆脱初始空间。我编写了以下代码（我对 VBA 完全陌生），但它似乎不起作用。当我在调试器中逐步执行它时，它看起来像一个无限循环。任何帮助，将不胜感激！
Sub DoTrim()
For Each cell In Selection.Cells
If cell.HasFormula = False Then
cell = Trim(cell)
End If
Next
End Sub
EDIT: It does look like the TRIM function is running into problems with a "space" character. This code:
编辑：看起来 TRIM 函数确实遇到了“空格”字符的问题。这段代码：
Sub DoTrim()
Dim cell As Range, areaToTrim As Range
Set areaToTrim = Selection.Cells
For Each cell In areaToTrim
cell.Value = "MUPPET"
Next cell
End Sub
Changed the value of the cells, so I guess it's a non-space whitespace! Any idea on how to get rid of those?
改变了单元格的值，所以我猜它是一个非空格空格！关于如何摆脱这些的任何想法？
回答by JimmyPena
This works well for me. It uses an array so you aren't looping through each cell. Runs much faster over large worksheet sections.
这对我很有效。它使用一个数组，因此您不会遍历每个单元格。在大型工作表部分上运行速度要快得多。
Sub Trim_Cells_Array_Method()
Dim arrData() As Variant
Dim arrReturnData() As Variant
Dim rng As Excel.Range
Dim lRows As Long
Dim lCols As Long
Dim i As Long, j As Long
lRows = Selection.Rows.count
lCols = Selection.Columns.count
ReDim arrData(1 To lRows, 1 To lCols)
ReDim arrReturnData(1 To lRows, 1 To lCols)
Set rng = Selection
arrData = rng.value
For j = 1 To lCols
For i = 1 To lRows
arrReturnData(i, j) = Trim(arrData(i, j))
Next i
Next j
rng.value = arrReturnData
Set rng = Nothing
End Sub
回答by Charles Williams
If you have a non-printing character at the front of the string try this
如果字符串前面有一个非打印字符，试试这个
Option Explicit
Sub DoTrim()
Dim cell As Range
Dim str As String
Dim nAscii As Integer
For Each cell In Selection.Cells
If cell.HasFormula = False Then
str = Trim(CStr(cell))
If Len(str) > 0 Then
nAscii = Asc(Left(str, 1))
If nAscii < 33 Or nAscii = 160 Then
If Len(str) > 1 Then
str = Right(str, Len(str) - 1)
Else
strl = ""
End If
End If
End If
cell=str
End If
Next
End Sub
回答by e100
Works fine for me with one change - fourth line should be:
只需进行一项更改就可以正常工作 - 第四行应该是：
cell.Value = Trim(cell.Value)
Edit: If it appears to be stuck in a loop, I'd add
编辑：如果它似乎陷入循环，我会补充
Debug.Print cell.Address
inside your For ... Nextloop to get a bit more info on what's happening.
在您的For ... Next循环中获取有关正在发生的事情的更多信息。
I also suspect that Trimonly strips spaces - are you sure you haven't got some other kind of non-display character in there?
我也怀疑Trim只去掉空格 - 你确定你那里没有其他类型的非显示字符吗？
回答by Pavel Skuhrovec
Worked for me perfectly as this:
完美地对我来说有效是这样的：
Trims all selected cells. Beware of selecting full columns/rows :P.
修剪所有选定的单元格。当心选择完整的列/行：P。
Sub TrimSelected()
Dim rng As Range, cell As Range
Set rng = Selection
For Each cell In rng
cell = Trim(cell)
Next cell
End Sub
回答by Johan van der Veen
Only thing that worked for me is this function:
唯一对我有用的是这个功能：
Sub DoTrim()
Dim cell As Range
Dim str As String
For Each cell In Selection.Cells
If cell.HasFormula = False Then
str = Left(cell.Value, 1) 'space
While str = " " Or str = Chr(160)
cell.Value = Right(cell.Value, Len(cell.Value) - 1)
str = Left(cell.Value, 1) 'space
Wend
str = Right(cell.Value, 1) 'space
While str = " " Or str = Chr(160)
cell.Value = Left(cell.Value, Len(cell.Value) - 1)
str = Right(cell.Value, 1) 'space
Wend
End If
Next cell
End Sub
回答by Tomalak
The infinite loop is a result of an On Error Resume Nextstatement somewhere higher up in your code. Get rid of it now. If your code only runs with On Error Resume Nextin effect, it needs instant and complete overhaul.
无限循环是On Error Resume Next代码中更高位置的语句的结果。现在摆脱它。如果您的代码仅有效运行On Error Resume Next，则需要立即彻底检修。
And while you are at it, place an Option Expliciton top of yor module. It forces you to declare all variables, a safeguard against annoying bugs that are the result of mis-typed variable names. (You can modify the VBA editor preferences to have that option set automatically the next time, which is highly recommended.)
当您使用它时，在您的Option Explicit模块顶部放置一个。它强制您声明所有变量，以防止由于错误键入变量名而导致的恼人错误。（您可以修改 VBA 编辑器首选项以在下次自动设置该选项，强烈建议这样做。）
The immediate problem with your code is that a cell is an object, and objects cannot be trimmed. You want to trim the text of the cell, so you must do so:
您的代码的直接问题是单元格是一个对象，并且无法修剪对象。你想修剪单元格的文本，所以你必须这样做：
cell.Text = Trim(cell.Text)
回答by Michael
This should accomplish what you want to do. I just tested it on a sheet of mine; let me know if this doesn't work. LTrim is if you only have leading spaces; the Trim function can be used as well as it takes care of leading and trailing spaces. Replace the range of cells in the area I have as "A1:C50" and also make sure to change "Sheet1" to the name of the sheet you're working on.
这应该完成你想做的事情。我刚刚在我的一张纸上测试了它；如果这不起作用，请告诉我。LTrim 是如果您只有前导空格；可以使用 Trim 功能以及它处理前导和尾随空格。将我拥有的区域中的单元格范围替换为“A1:C50”，并确保将“Sheet1”更改为您正在处理的工作表的名称。
Dim cell As Range, areaToTrim As Range
Set areaToTrim = Sheet1.Range("A1:C50")
For Each cell In areaToTrim
cell.Value = LTrim(cell.Value)
Next cell
回答by Marek
I would try to solve this without VBA. Just select this spaceand use replace (change to nothing) on that worksheet you're trying to get rid off those spaces.
我会尝试在没有 VBA 的情况下解决这个问题。只需选择此空间并在您试图摆脱这些空间的工作表上使用替换（更改为空）。
If you really want to use VBA I believe you could select first character
如果你真的想使用 VBA 我相信你可以选择第一个字符
strSpace = left(range("A1").Value,1)
and use replace function in VBA the same way
并以相同的方式在 VBA 中使用替换功能
Range("A1").Value = Replace(Range("A1").Value, strSpace, "")
or
或者
for each cell in selection.cells
cell.value = replace(cell.value, strSpace, "")
next
