Trim Cells help needed.

[robrian]

Hello.

I have a sheet that im dumping a large report into and then running a macro to clean it up for import into another application. Im needing to select all the cells in the sheet and then run the trim function to kill off extra spaces so they done get imported in the other application.

Tried a couple different times and it keeps erroring out. Should be simple but i'm thinking I just got the syntax wrong. Would appreciate any help that I can get thanks.

This was my meager attempt:

cells.select
selection.trim


thanks.

 

[taurean]

You can do this by using code like

Sub Trim()
With Selection
    .Value = Application.Trim(.Value)
End With
End Sub

However there are some limitations associated with it. Here's a thread which should help you.
http://www.mrexcel.com/forum/excel-questions/638331-trim-using-macro-2.html

 

[MARK858]

For a basic VBA Trim (if that is all that is required (in other words blank spaces only and not non-printing characters) then try...

Sub trm()
Dim cll As Range
        For Each cll In Cells.SpecialCells(2, 3)
            cll.Value = Trim(cll.Value)
        Next cll
End Sub

Edit: very similar to what taurean just posted but leaving it here.

 

[robrian]

Thanks so much exactly what i needed....