Trim help


A

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
 
Ad

Advertisements

M

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
 
Ad

Advertisements

A

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top