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

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" wrote:

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

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?

"Mike H" wrote:

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" wrote:

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

try this


'These instructions pre typed & are worded to cater for the novice
programmer

'To install macro to correct location

'Copy this macro
'GoTo Excel
'Open VB Editor by pressing Alt + F11
'Just below the menus & toolbars on the left you should see Project -
VbaProject window
'In this window you will see ThisWorkbook
'double click on ThisWorkbook - opens ThisWorkbook module sheet
'Paste macro code into big window on right

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim wS As Worksheet
Dim c As Range
For Each wS In Worksheets
For Each c In wS.UsedRange
If Not c.HasFormula Then
c.Value = Trim(c.Value)
End If
Next c
Next wS
ThisWorkbook.Save
End Sub


--
mudraker
------------------------------------------------------------------------
mudraker's Profile: http://www.thecodecage.com/forumz/member.php?userid=18
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=23224