在Excel中使用VBA修剪单元格 [英] Trim Cells using VBA in Excel

问题描述

我在Excel中有一些数据似乎是一个简单的问题。我有很多数据与从Web表格粘贴的前导空格，我想摆脱初始空间。我列举了以下代码（我完全是VBA的新功能），但似乎不起作用。当我在调试器中通过它看起来像一个无限循环。任何帮助将不胜感激！

  Sub DoTrim（）
对于每个单元格在Selection.Cells 
如果cell.HasFormula = False然后
 cell = Trim（cell）
 End If 
 Next 
 End Sub 
  

编辑：
它看起来像TRIM函数正在运行到一个空格字符的问题。此代码：

  Sub DoTrim（）
 Dim cell As Range，areaToTrim As Range 
 Set areaToTrim = Selection.Cells 
对于每个单元格在areaToTrim 
 cell.Value =MUPPET
下一个单元格
 End Sub 
  

更改了单元格的值，所以我猜这是一个非空格的空格！任何想法如何摆脱那些？

解决方案

如果您在字符串前面有非打印字符，请尝试这个

  
 Option Explicit 
 Sub DoTrim（）
 Dim cell As Range 
 Dim str As String 
 Dim nAscii As Integer 
对于每个单元格在Selection.Cells 
如果cell.HasFormula = False然后
 str = Trim（CStr（cell） ）
如果Len（str）> 0然后
 nAscii = Asc（Left（str，1））
如果nAscii <33或nAscii = 160 Then 
如果Len（str） >然后
 str = Right（str，Len（str） -  1）
 Else 
 strl =
 End If 
 End If 
 End如果
 cell = str 
 End If 
 Next 
 End Sub 
  

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!

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:

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?

解决方案

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

这篇关于在Excel中使用VBA修剪单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！