How does Trim work in Excel VBA?

Question

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

It gives:

Error - Object doesn't support this property method

score 1 · Answer 1 · answered Feb 05 '13 at 01:53

You cannot use Trim on a range. The Trim documentation states:

Trim(string)
The required string argument is any valid string expression.

Also, Trim is not suited to your task in that it does NOT remove spaces within a string. This is mentioned in the documentation (emphasis added):

Returns a Variant (String) containing a copy of a specified string without leading spaces (LTrim), trailing spaces (RTrim), or both leading and trailing spaces (Trim).

score 1 · Accepted Answer · answered Feb 05 '13 at 15:33

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

Floris · Answer 3 · 2013-02-05T15:33:21.960

0

Since Trim does not support a range, you could add the following:

Dim c as Cell
For each c in .Cells
  c.Value = Trim(c.Value)
Next c

Disclaimer: not tested. See other answer for more complete solution.

edited Feb 05 '13 at 15:33

answered Feb 05 '13 at 03:35

Floris

45,857
6
70
122

Thax Whats the issue with following code Sub Trim() ' Remove leading spaces =RIGHT(A1, LEN(A1)-1) Dim intCounter As Integer For intCounter = 1 To Sheets("Sheet1").UsedRange.End(xlDown).Row Range("A" & intCounter).Value = Trim(Range("A" & intCounter).Value) Next intCounter code – shabar Feb 05 '13 at 05:18
Sorry ONLY following Dim intCounter As Integer For intCounter = 1 To Sheets("Sheet1").UsedRange.End(xlDown).Row Range("A" & intCounter).Value = Trim(Range("A" & intCounter).Value) Next intCounter code – shabar Feb 05 '13 at 05:58
1

You define a sub Trim() which doesn't take a parameter, and that calls a function Trim() that does... You could fix that by changing the name of the sub to `myTrim()` or something like that. Note that if your UsedRange is one row high, your sub will fail. Test if number of rows of UsedRange > 1 - treat that as a separate case. – Floris Feb 05 '13 at 15:06

score -1 · Answer 4 · edited Dec 09 '15 at 20:35

Sub DescTrim(Cx As Integer)  
    Dim Rx As Integer: Rx = 5 'Start at row 5  
    Dim STrimmed As String  
        For Rx = 5 To ActiveSheet.UsedRange.Rows.Count  
            STrimmed = Trim(Cells(Rx, Cx).Value)  
            While InStr(STrimmed, chr(32) & (chr(32))  
                STrimmed = Replace(STrimmed, Chr(32) & Chr(32), chr(32))  
            Wend  
            Cells(Rx, Cx).Value = STrimmed  
        Next  
End Sub

How does Trim work in Excel VBA?

4 Answers4

Linked