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

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.

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.

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 -

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 -

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 -

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 -

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 -