Removing leading and trailing space...

[Jeffery B Paarsa]

Hello,

I have a multi sheet excel spread sheet and I would like to remove the
leading and trailing space from all the Cells of these sheets. TRIM(A&i)
only does it on a single column how can this be done on all the columns and
sheets in a single run?

Any sample code/iead?

 

[Dave Peterson]

Option Explicit
Sub testme()

Dim myRng As Range
Dim myCell As Range
Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
Set myRng = Nothing
On Error Resume Next
Set myRng = wks.Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "nothing to fix--no constants on " & wks.Name & "."
Else
For Each myCell In myRng.Cells
'myCell.Value = Trim(myCell.Value)
'or
myCell.Value = Application.Trim(myCell.Value)
Next myCell
End If
Next wks

End Sub

There's a difference between the way VBA's Trim works and the way Excel's
=trim() works.

If your text were:
....Jeffery....B.....Paarsa....
(spaces represented by dots)

Then VBA's trim will return:
Jeffery....B.....Paarsa

Application.trim() will return:
Jeffery.B.Paarsa

Application.trim() cleans up those multiple internal spaces.

 

[Per Jessen]

Hi Jeff

Try this:

Sub TrimAll()
Dim sh As Worksheet
Dim cell As Range
Application.ScreenUpdating = False
For Each sh In ActiveWorkbook.Sheets
For Each cell In sh.UsedRange
cell = Trim(cell.Value)
Next
Next
Application.ScreenUpdating = True
End Sub

Regards,
Per