Removing leading and trailing space...
 

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?

--
Jeff B Paarsa

Removing leading and trailing space...
 

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 we
....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.

Jeffery B Paarsa wrote:

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?

--
Jeff B Paarsa

--

Dave Peterson

Removing leading and trailing space...
 

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


On 8 Okt., 22:18, Jeffery B Paarsa wrote:
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?

--
Jeff B Paarsa