Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formatting numbers with leading and trailing zero's | Excel Worksheet Functions | |||
Excluding only leading or trailing cells from a column | Excel Worksheet Functions | |||
Removing leading/trailing spaces | Excel Discussion (Misc queries) | |||
How keep Leading/Trailing spaces when save in Excel? | Excel Discussion (Misc queries) | |||
Trailing Space / Format Question | Excel Discussion (Misc queries) |