Conditional format for removing trailing and leading spaces.

[Eamonn100]

Hi,

I'm using this formula to remove any trailing or leading spaces. =TRIM(CLEAN(A1)) It works fine but is there anyway that I can use it as a conditional format so that when I paste the information onto the page it removes any trailing or leading spaces.

At the moment I have to use this formula on a second page to get the spaces removed. I'm trying to get this on to my original page that I paste onto because other formulas are returning error when reading from the second sheet.

Thanks in advance.

 

[JACOBB9900]

Hi i came across your VBA code above. I am still a green horn and have not ventured too far into VBA but does your code apply to all cells on the worksheet? If so could you help me apply it to just a single column. Column C.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Application.EnableEvents = False
For Each cell In Target
cell.Value = Trim(cell.Value)
Next cell
Application.EnableEvents = True
End Sub

Thank you.

 

[Joe4]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    
    Set rng = Intersect(Target, Columns("C:C"))
    
    If rng Is Nothing Then Exit Sub

    Application.EnableEvents = False
    For Each cell In rng
        cell.Value = Trim(cell.Value)
    Next cell
    Application.EnableEvents = True

End Sub

 

[JACOBB9900]

Thank you but when I ran the code this popped up. Usually the name of the code is displayed so i can click on.

 

[MARK858]

You don't run the code, you make a manual change to a cell in column C of the sheet and the code runs automatically.

 

[Joe4]

If you put the code in the propert Worksheet module (the sheet that you want it to run against), it will run automatically when you make a change to your sheet.
If it does not run automatically, then you either put it in the wrong place, or have not enabled Macros/VBA to run.

 

[JACOBB9900]

Thank you MARK858 and Joe4. I hadn't saved it as a macro enabled file. I really appreciate the help. It has saved me a lot of time.