Trim help

[akemeny]

Hi,

I have a large spreadsheet (columns A through bx). I have tried to find a
macro that run automatically and trim the contents of the cells.

I need it to:

1. Delete blank spaces before the contents of the cell (whether text,
number or date)
2. Skip over cells with formulas
3. Delete blank spaces after the contents of the cell

Any help is greatly appreciated

 

[Mike H]

Hi,

This may take a few moments to run on a larger range. Right click your sheet
tab, view code and paste this in and run it

Sub trimit()
For Each c In ActiveSheet.UsedRange
If Not c.HasFormula Then
c.Formula = Trim(c.Formula)
End If
Next
End Sub

Mike

 

[akemeny]

That worked perfectly. I have one issue though. I'm trying to get it to run
automatically on all the worksheets when I close the workbook, but for some
reason its not working. I can get it to run when I activate the worksheet,
but it does take a long time to process through all the active cells. Do you
have any suggestions for getting it to run when the workbook is being closed?