4

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
Alex Knauth
  • 8,133
  • 2
  • 16
  • 31
teaspoon
  • 115
  • 1
  • 1
  • 12
  • because your R1C1 are within the quotes, they do not change, You need to set up a loop with a counter, and that counter becomes part of your address. – Forward Ed May 03 '16 at 14:03

5 Answers5

11

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.

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • 1
    +1 Good point on adding in the [Intersect method](https://msdn.microsoft.com/en-us/library/office/aa195772.aspx). –  May 03 '16 at 14:13
  • 1
    @Jeeped that's just in case the user selects the whole column ! – Gary's Student May 03 '16 at 14:29
  • Yes, I've been proselytizing its use in UDFs of late so that full column or row references can be used without detriment. We cannot complain too loudly about using full column references in native functions like SUMPRODUCT unless we clean up our own UDFs first. –  May 03 '16 at 14:37
  • @garys-student thanks, it is more complicated than it seems. :-( Also, when I perform it on the whole column it it freezes on me (I am on Excel 2016), I have to Esc out of it – teaspoon May 04 '16 at 08:17
4

While there is a native VBA Trim function, it only removes spaces from the left and right ends. It does not also change double spaces between words to a single space. To do that, you can use the WorksheetFunction object and the worksheet's TRIM function.

Sub myTrim()
    dim rng as range
    for each rng in Selection
        'use only one of these
        rng = Trim(rng.Value)
        rng = WorksheetFunction.Trim(rng.Value)
    next rng 
End Sub

See How to avoid using Select in Excel VBA macros for more boilerplate on running code against the Application.Selection property.

For quick full column trimming, a Range.TextToColumns method works very well.

Sub myTrim2()
    Dim col As Range
    For Each col In Selection.Columns
        col.TextToColumns DataType:=xlFixedWidth, FieldInfo:=Array(0, 1)
    Next col
End Sub

This latter method does not remove internal double spaces; only leading and trailing spaces.

It is not a good idea to name your procedures and/pr functions the same as existing functions unless you intentionally want to overwrite the functionality.

Community
  • 1
  • 1
  • OK, I get it, I created 2 macros with one of the lines (Trim) and (WorksheetFunction.Trim) in case I want to only trim the leading and trailing spaces... so I take it that it's the same solution as garys-student's, only without the Intersect() which didn't make any visible difference anyway... But the myTrim2 for column trimming doesn't seem to work :-( – teaspoon May 04 '16 at 08:38
1

Try:

Private Sub Worksheet_Change(ByVal Target as Range)

Target.Value = Trim(Target.Value)

End Sub

This will change any cell you change in the sheet to a trimmed version of the cell value.

Jordan
  • 4,424
  • 2
  • 18
  • 32
0

just try with immediate window

?Application.Trim(" teaspoon ")

teaspoon

?WorksheetFunction.Trim(" teas poon ")

teas poon

?Trim(" tea spoon ")

tea spoon

Karthick Gunasekaran
  • 2,697
  • 1
  • 15
  • 25
0

Will not this do the trick?

Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False

Would this be slower than the intersect?

Eilthalearin Kheru
  • 19
  • 4