Removing leading and trailing space...

  • Thread starter Jeffery B Paarsa
  • Start date

J

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?
 
Ad

Advertisements

D

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

Advertisements

P

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
 

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