TRIM Macro - Syntax Explanation

[Jason Campbell]

Hi All,

I have come across this cracking macro to quickly iron out extra spaces inputted erroneously by users! The thing is it removes the additional spaces on the Left, Middle & to the Right of the text entry.

Can anyone help me interpret these specific parts of macro, bits highlighted in red & purple, shown directly below?

Selection.Cells.SpecialCells(xlCellTypeConstants, 23).Select --> What dose the (xlCellTypeConstants, 23) part of syntax mean?

How do the spaces and speech marks come into play below, when trimming the left, middle and right of the text?
(Trim(MyCell.Value), " ", " ")
(Trim(MyCell.Value), " ", " ")
(Trim(MyCell.Value), " ", " ")
(Trim(MyCell.Value), " ", " ")


Any explanation to help me understand what is being specified here would gratefully be received.

Thanks in advance.

---------------------------------------------------------------------------------------------------------------------------------------------
Full working macro syntax below:

Sub TrimText()
' This module will trim extra spaces from BOTH SIDES and excessive spaces from inside the text.
    Dim MyCell As Range
    On Error Resume Next
        Selection.Cells.SpecialCells(xlCellTypeConstants, 23).Select
        For Each MyCell In Selection.Cells
            MyCell.Value = Application.WorksheetFunction.Substitute(Trim(MyCell.Value), "     ", " ")
            MyCell.Value = Application.WorksheetFunction.Substitute(Trim(MyCell.Value), "    ", " ")
            MyCell.Value = Application.WorksheetFunction.Substitute(Trim(MyCell.Value), "   ", " ")
            MyCell.Value = Application.WorksheetFunction.Substitute(Trim(MyCell.Value), "  ", " ")
        Next
    On Error GoTo 0
End Sub

 

[steve the fish]

Cell type constants are just that, constants. So they aren't formulas. The substitute(trim( etc etc is unnecessary. Just use

worksheetfunction.trim(myCell.value)

The worksheet function trim will completely delete spaces found to the left or right of your value and also delete any contiguous spaces found within your value. So therefore:

        my       value

becomes

my value

 

[RoryA]

As Steve said, xlCellTypeConstants will restrict the selection to cells that contain constants, rather than formulas or blanks.

The second argument (23) is what kind of constants - the available options, and their values, are:
xlErrors - 16
xlLogical - 4
xlNumbers - 1
xlTextValues - 2

and you can combine those in any fashion by adding the values - 23 is all of them.

Also, as Steve said, the last part of the function can be simplified by using the Trim worksheet function instead:

Sub TrimText()
' This module will trim extra spaces from BOTH SIDES and excessive spaces from inside the text.
    Dim MyCell                As Range
    On Error Resume Next
    For Each MyCell In Selection.Cells.SpecialCells(xlCellTypeConstants, 23).Cells
        MyCell.Value = Application.WorksheetFunction.Trim(MyCell.Value)
    Next
    On Error GoTo 0
End Sub

 

[Jason Campbell]

Gents,

Thanks for the explanation. It's amazing how much clarity can be gained when explanations are given with examples...especially when trying to teach yourself a new skill!

Can't believe how much simpler the code has become in one swoop!

Thanks again.

Jason