remove leading spaces
 

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.

remove leading spaces
 

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

On Jan 24, 11:24*am, harwookf
wrote:
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.

remove leading spaces
 

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.



"Pete_UK" wrote:

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

On Jan 24, 11:24 am, harwookf
wrote:
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.

remove leading 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

On Jan 24, 4:03*pm, harwookf
wrote:
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.



"Pete_UK" wrote:
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

On Jan 24, 11:24 am, harwookf
wrote:
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.- Hide quoted text -

- Show quoted text -

remove leading spaces
 

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



"Pete_UK" wrote:

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

On Jan 24, 4:03 pm, harwookf
wrote:
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.



"Pete_UK" wrote:
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

On Jan 24, 11:24 am, harwookf
wrote:
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.- Hide quoted text -

- Show quoted text -

remove leading spaces
 

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


On Jan 24, 4:37*pm, harwookf
wrote:
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



"Pete_UK" wrote:
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

On Jan 24, 4:03 pm, harwookf
wrote:
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.

"Pete_UK" wrote:
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

On Jan 24, 11:24 am, harwookf
wrote:
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.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

remove leading spaces
 

This worked perfectly. Many thanks for all your help.

"Pete_UK" wrote:

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


On Jan 24, 4:37 pm, harwookf
wrote:
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



"Pete_UK" wrote:
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

On Jan 24, 4:03 pm, harwookf
wrote:
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.

"Pete_UK" wrote:
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

On Jan 24, 11:24 am, harwookf
wrote:
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.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

remove leading spaces
 

You're welcome - thanks for feeding back.

Pete

On Jan 25, 1:25*pm, harwookf
wrote:
This worked perfectly. Many thanks for all your help.



"Pete_UK" wrote:
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

On Jan 24, 4:37 pm, harwookf
wrote:
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

"Pete_UK" wrote:
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

On Jan 24, 4:03 pm, harwookf
wrote:
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.

"Pete_UK" wrote:
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

On Jan 24, 11:24 am, harwookf
wrote:
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.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -