修剪在Excel VBA中如何工作？ [英] How does Trim work in Excel VBA?

问题描述

我有以下代码来摆脱中间空格，换行和修剪

I have got following code for get rid of middle spaces, line feed and Trim

但是修剪不工作可能是什么原因？

But Trim doesn't work. What could be the reason?

Sub Replace()

  With Sheets("Input_Data").Range("A1:A6300")
  'With Sheets("Input_Limits").Range("A1:A6300")

    .Cells.Replace "  ", " ", xlPart, xlByRows, False
    .Cells.Replace vbLf, "", xlPart, xlByRows, False
    '.Cells.Trim

  End With

End Sub

它给出：

错误 - 对象不支持此属性方法

Error - Object doesn't support this property method

推荐答案

以下代码将做你要求的请注意，我正在将修剪操作应用于活动工作表中的所有单元格;如果这不是你想要的，你可以明显地编辑它...

The following code will do what you are asking for. Note that I am applying the trim operation to all cells in the active sheet; if that is not what you want, you can obviously edit it...

Sub trimAll()
  Dim c As Range

  For Each c In ActiveSheet.UsedRange.Cells
    If Not (IsEmpty(c) Or IsError(c) Or IsFormula(c)) Then
      c.Value = Trim(c.Value)
    End If
  Next c

  With ActiveSheet.UsedRange.Cells
    .Cells.Replace "  ", " ", xlPart, xlByRows, False
    .Cells.Replace vbLf, "", xlPart, xlByRows, False
  End With

End Sub

Function IsFormula(c)
  ' use this to test for formulas - don't edit those or they become values!
  IsFormula = True
  On Error Resume Next
  If Left(c.Formula, 1) = "=" Then IsFormula = True Else IsFormula = False
End Function

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