remove leading spaces

  • Thread starter Thread starter harwookf
  • Start date Start date
H

harwookf

Is there a way of automatically being able to remove leading spaces? A
number of people work on a spreadsheet and some remove the space themselves,
but others don't.
Ideally I would like it to automatically change perhaps by using a macro.
 
You can use the TRIM function to remove leading (and trailing) spaces
- in a helper column put a formula like:

=TRIM(A1)

and copy down, assuming your values are in column A - adjust to suit.
You can fix the values and then copy over the originals. Record a
macro while you do this once, and then you can replay it again next
time you want to do this.

Hope this helps.

Pete
 
Is it possible for this to happen automatically without having to click on
anything for it to work??
I already have VBA code to change the colour of cells and to capitalize
columns. Ideally I would like to just alter this to deal with the spaces.
 
You could apply it at the same time as you capitalize each cell in the
column. Post your code if you need further advice.

Pete
 
Here is my current code. Appreciate your help.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rArea As Range
Dim rCell As Range
Dim nColor As Long
Dim Rng1 As Range
Dim Rng2 As Range

Set Rng1 = Intersect(Target, Me.Range("E:E"))
Set Rng2 = Intersect(Target, Me.Range("C:C", "D:D"))

On Error GoTo ws_exit

'do the column E stuff
If Not (Rng1 Is Nothing) Then
For Each rCell In Rng1.Cells
Select Case rCell.Value
Case 0, 100
nColor = RGB(255, 0, 0)
Case 30
nColor = RGB(23, 178, 233)
Case 60
nColor = RGB(245, 200, 11)
Case 90
nColor = RGB(0, 255, 0)
Case Else
nColor = RGB(255, 255, 255)
End Select

If Not nColor = -1 Then
rCell.Offset(0, -4).Interior.Color = nColor
Else
rCell.Offset(0, -4).Interior.ColorIndex = _
xlColorIndexNone
End If

Next rCell
End If

'do the column B stuff
If Not (Rng2 Is Nothing) Then
Application.EnableEvents = False
For Each rCell In Rng2.Cells
With rCell
If Not .HasFormula Then
.Value = UCase(.Value)
End If
End With
Next rCell
End If

ws_exit:
Application.EnableEvents = True

End Sub
 
Towards the end where you have:

.Value = UCase(.Value)

you can make this:

.Value = Trim(UCase(.Value))

and this should take care of the leading and trailing spaces.

Hope this helps.

Pete
 
Back
Top