Trim using Macro

[MASOODAHMAD]

Hi,

I would like to remove any trailing spaces from a number of columns on sheet 1. Please suggest a good macro.

Thanks,

Masood

 

[ZVI]

I tried the below code, but got an error

Application.Trim function returns error if string contains more than 255 chars.
For the common case try this:

Sub MyTrim1()
  Dim a(), c&, cs&, r& 
  With ActiveSheet.UsedRange
    a() = .Value
    cs = UBound(a, 2)
    For r = 1 To UBound(a)
      For c = 1 To cs
        If VarType(a(r, c)) = vbString Then
          a(r, c) = Trim(a(r, c))
        End If
      Next
    Next
    .Value = a()
  End With
End Sub

 

[Mike Blackman]

Hi Again,

I added in the Application parts as they will improve the speed of the sub (depending on if you have events & calculations reading the data set the Enable Events & Calculation lines may not be required). So taking away these the code really isn't that lengthy. I also got the impression the columns your working with may not be contiguous, ie if you only needed to trim off values in columns 7, 9 & 12 then this would work nicely.

Sub TrimMyDefinedColumns()

        Dim arrColumns()
        Dim lngColumn As Long, lngLastRow As Long, lngRow As Long
                
        arrColumns = Array(7, 9, 12)
        
        With Sheet1
                For lngColumn = 0 To UBound(arrColumns)
                        With .Columns(arrColumns(lngColumn))
                                lngLastRow = .Cells(Rows.Count, 1).End(xlUp).Row
                                For lngRow = 1 To lngLastRow
                                        .Cells(lngRow, 1).Value = Trim(.Cells(lngRow, 1).Value)
                                Next lngRow
                        End With
                Next lngColumn
        End With

End Sub

I love ZVI's initial response, i tried something nearly identical but didn't reference the Application.Trim and it fell over so reverted to a loop. If i was to have to use one of the loops I do like mine, I feel it would potentially be a little more efficient then looping the entire UsedRange.

 

[Jon von der Heyden]

An alternative will be use the call the TRIM worksheet function from a range, thus use a helper sheet.

The following seems to work. Just be aware that this will overwrite any formula in the sheet (i.e. they will become constants).

Public Sub TrimAll()
    Dim rngUsedRange As Range
    Dim wkbTemp As Workbook
    Dim rngCol As Range
    
    Const strFormula = "=IF(LEN([ADDRESS]),TRIM([ADDRESS]),"""")"
    
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    
    Set rngUsedRange = ActiveSheet.UsedRange
    Set wkbTemp = Workbooks.Add(Template:=xlWBATWorksheet)
    
    With wkbTemp.Sheets(1).Range(rngUsedRange.Address)
        .Formula = Replace$(strFormula, "[ADDRESS]", rngUsedRange.Address(External:=True))
        .Copy
        Call ThisWorkbook.ActiveSheet.UsedRange.PasteSpecial(Paste:=xlValues)
    End With
    
    Call wkbTemp.Close(SaveChanges:=False)
    
    For Each rngCol In rngUsedRange.Columns
        Call rngCol.TextToColumns(Destination:=rngCol, DataType:=xlDelimited)
    Next rngCol
    
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub

 

[Jon von der Heyden]

I wonder if there are any other "worksheet functions" that work differently when descended from the Application object as opposed to WorksheetFunction object? More importantly, I wonder if there is any documentation describing this different functionality?

Application.Vlookup/Hlookup/Match behave differently in that if no match is found; if from a WorksheetFunction Object then a RT error occurs, otherwise it actually yields the error value... I thought Colin Legg blogged it but I don't see it there... Maybe he just told me about it here...

 

[MASOODAHMAD]

I got a "Runtimer error 13" using the below code:

Sub MyTrim()
With ActiveSheet.UsedRange
.Value = Application.Trim(.Value)
End With
End Sub

@Mike, using your both code, I got an error "Can't assign to array"
and it points to the array
"arrColumns = Array(1, 3)"


Okay, let's start from the beginning. I would like to use the VBA code to remove leading and trailing spaces from the selected columns which is Column A to Column E.

Please advise something urgent.

@Lynn, I tried your reference, but the tools are not made for Mac OS. I work on both systems, Mac as well as PC.

Thanks

 

[MASOODAHMAD]

Leave it, I got the code and here it is. I have tested it, it worked well with selected range.


Option Explicit

Sub TrimXcessSpaces()
'Macro Purpose: To trim all excess spaces out of cells. This
'eliminates issues where users have cleared the cell with a space,
'and elimates all extra spaces at the beginning or end of a string

Dim cl As Variant

'Loop through cells removing excess spaces
For Each cl In Selection
If Len(cl) > Len(WorksheetFunction.Trim(cl)) Then
cl.Value = WorksheetFunction.Trim(cl)
End If
Next cl
End Sub</pre>

 

[Rick Rothstein]

Application.Trim function returns error if string contains more than 255 chars.

Well but of course... its Excel, there has to be an inconvenient limit somewhere just waiting to bite you on the a$$.

 

[Rick Rothstein]

'Macro Purpose: To trim all excess spaces out of cells. This
'eliminates issues where users have cleared the cell with a space,
'and elimates all extra spaces at the beginning or end of a string
[

Just to make sure you are completely aware, WorksheetFunction.Trim also collapses multiple internal spaces down to single spaces. For example, this...

"    One      Two        Three    "

becomes this after WorksheetFunction.Trim is applied to it...

"One Two Three"

If you want to avoid this, just use VB's built in Trim function inplace of WorksheetFunction.Trim instead.

 

[Jerry Sullivan]

Application.Trim function returns error if string contains more than 255 chars.

One other limitation of this kind of Application. function is that it will return a Type Mismatch error if the Range specified is more than 65536 rows.

Sub Test1() [COLOR="Teal"]'This works on this range up to 65536 Rows[/COLOR]
    With Range("A1:A65536")
        .Value = Application.Trim(.Value)
    End With
End Sub

Sub Test2() [COLOR="teal"]'This range generates Type mismatch Run-time error[/COLOR]
    With Range("A1:A65537")
        .Value = Application.Trim(.Value)
    End With
End Sub

A work-around is to iterate through ranges with more rows in Steps of 65536 rows.