Remove unnecessary spaces in cells in column "J"

[harzer]

Hello everyone,
How can we remove (Quickly) all unnecessary spaces: at the beginning, at the end and inside the cells that are in the cells of column "J".
We start at line 2 in "J2" and end at the last cell of column "J".
When I talk about inside the cells in column "J", I mean remove the duplicate spaces so that there is only one space between each word in the cells in column "J".
I remain at your disposal for other additional information if necessary.
Good programming.
Thank you for your contributions.

 

[Joe4]

This VBA should do what you want:

Sub RemoveExtraSpaces()

    Dim lr As Long, r As Long
    
    Application.ScreenUpdating = False
    
'   Find last row in column J with data
    lr = Cells(Rows.Count, "J").End(xlUp).Row
    
'   Loop through all cells in column J starting with line 2
    If lr > 1 Then
        For r = 2 To lr
'           See if there is an entry in column J
            If Cells(r, "J") <> "" Then
'               Trim extra spaces
                Cells(r, "J").Value = Application.Trim(Cells(r, "J").Value)
            End If
        Next r
    End If

    Application.ScreenUpdating = True
    
End Sub

 

[mumps]

This code was written by jindon.

Sub RemoveSpaces()
    With ActiveSheet.Columns("J:J")
        .Value = Evaluate("if(row(" & .Address & "),clean(trim(" & .Address & ")))")
    End With
End Sub

 

[Joe4]

This code was written by jindon.

Sub RemoveSpaces()
    With ActiveSheet.Columns("J:J")
        .Value = Evaluate("if(row(" & .Address & "),clean(trim(" & .Address & ")))")
    End With
End Sub

Only caveat there is that includes row 1, and they wanted to start at row 2.
Depending on what is in cell J1, it may or may not matter.

 

[mumps]

How about:

Sub RemoveSpaces()
    With ActiveSheet.Range("J2", Range("J" & Rows.Count).End(xlUp))
        .Value = Evaluate("if(row(" & .Address & "),clean(trim(" & .Address & ")))")
    End With
End Sub

 

[harzer]

Hello mumps & Joe4,
Thank you for your respective codes.
Both codes give me the results I want and they are fast.
Thank you both for your quick responses.
Greetings.

 

[mumps]

Glad we could help.