TRIM function not working

[Manolocs]

I have no idea why this happens, I already followed threads from this forum and use the following formulas and macros but still can not get ride of the spaces in front of the cells
Any help is more than welcome.


=TRIM(A1)
=SUBSTITUTE(A1,CHAR(160),"")
=TRIM(SUBSTITUTE(D1,CHAR(160),CHAR(32)))

Sub RemoveLeadingSpace()
'Updateby20131129
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
    Rng.Value = VBA.LTrim(Rng.Value)
Next
End Sub

 

[Manolocs]

Here is the spreadsheet the column A is done by TRIM but the others don't

https://dl.dropboxusercontent.com/u/14313867/Workbook1.xlsx

Thank you gentlemen for your time.

 

[RoryA]

Use:

Sub RemoveLeadingSpace()
'Updateby20131129
    Dim Rng                   As Range
    Dim WorkRng               As Range
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
    WorkRng.Replace Chr(160), Chr(32), xlPart
    For Each Rng In WorkRng
        Rng.Value = VBA.LTrim(Rng.Value)
    Next
End Sub

 

[Manolocs]

Hi RoryA, still does not work....
any other idea?

 

[RoryA]

I ran that on your sample file and it worked fine for me.

 

[Manolocs]

I ran that on your sample file and it worked fine for me.

I run in the same file but only change the column A the other three columns remain with spaces
I don't understand how can work with you and not mine

 

[RoryA]

Did you select all the columns when you ran it?

 

[Manolocs]

Yes and still only the column A got rid of the spaces the others still the same.

 

[RoryA]

Try selecting just column B, then running it (make sure you're running the version I just posted, not your original) and see what happens.

 

[Trebor76]

This worked fine for me:

Option Explicit
Sub Macro1()

    Dim lngLastRow As Long
    Dim rngMyCell As Range
    
    lngLastRow = Range("A:E").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    Application.ScreenUpdating = False
    
    For Each rngMyCell In Range("A1:E" & lngLastRow)
        If Len(rngMyCell) > 0 And IsNumeric(rngMyCell) = False And rngMyCell.HasFormula = False Then
            rngMyCell = Trim(rngMyCell)
        End If
    Next rngMyCell
    
    Application.ScreenUpdating = True

End Sub

HTH

Robert

 

[Manolocs]

Hi Trebor76, does not work as well it remove only the leading spaces of column A, the other remain the same