Perform trim function on the selected cells in VBA

Question

I am trying to figure out if there is a way I could run a simple function (TRIM to remove duplicate space characters) in an Excel macro so it is performed directly on the cell and so I don't have to create an extra column with the function that TRIMs the previous column.

It should work on selected column or just selected cells

Sub trim()

 ActiveCell.FormulaR1C1 = "=trim(R1C1)"

End Sub

Answer 1

Give this a try:

Sub TrimAndFit()
    Dim r As Range

    With Application.WorksheetFunction
    For Each r In Intersect(Selection, ActiveSheet.UsedRange)
        r.Value = .Trim(r.Value)
    Next r
    End With
End Sub

This will work on all Selected cells. We use Intersect() to improve performance.