Trim Function As Macro

[Dazzawm]

Is it possible to select an entire worksheet and run the trim function as a macro? I have about 10,000 rows and 20 columns of data so doing each column individually and copy/pasting etc will be very time consuming. I have do it on several files too.

 

[schielrn]

Have a look at downloading ASAP Utilities. There are a ton of valuable tools within there. I couldn't imagine not having it. But you could create a macro to do this as well as such:

sub trimCells()
for each c in activesheet.usedrange
c.value = trim(c.value)
next c
end sub

This is untested, but should work, I can't test it at the moment.

Hope that helps.

 

[Dazzawm]

That doesnt seem to work properly. I am correct in saying it gets rid of extra spaces between words etc? If so, I still have extra spaces in a few cells I know of.

 

[meldoc]

Hello

The VBA version of Trim, works differently from the Excel version, try this.

Sub trimCells()
  For Each c In ActiveSheet.UsedRange
    c.Value = WorksheetFunction.Trim(c.Value)
  Next c
End Sub

 

[schielrn]

That doesnt seem to work properly. I am correct in saying it gets rid of extra spaces between words etc? If so, I still have extra spaces in a few cells I know of.

As stated by Meldoc the vba version of trim does not get rid of excessive spaces between words. He has provided a solution that should worka dn I would still recommend having a look at ASAP Utilities, which is free.

 

[Dazzawm]

Thanks Meldocs code did work. So what was the other code going to do?

 

[Scott Huish]

The VBA trim function only trims spaces off the left and right side of the string. It does not remove extra spaces between words, you need the worksheet function TRIM for this.