Share via

How do you use the TRIM command in VBA?

Anonymous
2012-06-05T20:56:06+00:00

Hello to you masters of VBA.  I have a spread sheet that has data in many rows in columns A thru E.   I am trying to write a macro that run the TRIM command and remove any spaces in the occupied cells.  Can anyone help me?

Thanks

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2012-06-05T22:51:25+00:00

    Here is some code that trims the cells in the current selection. Not that it can only trim constants and not the values returned by formulas. For that you would just modify the formulas.

    Public Sub TrimSelection()

        Dim rng As Range

        Dim rngAll As Range

        On Error Resume Next

        Set rngAll = Selection.SpecialCells(xlCellTypeConstants)

        On Error GoTo 0

        If rngAll Is Nothing Then

            MsgBox "sorry... no constants to trim"

        Else

            For Each rng In rngAll

                rng.Value = Trim(rng.Value)

            Next rng

        End If

    End Sub

    4 people found this answer helpful.
    0 comments
  2. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2012-06-05T23:50:38+00:00

    Hi,

    The Excel TRIM() function is more powerful than the VBA TRIM() function.  The Excel TRIM() function gets rid of redundant spaces between words as well.  The VBA TRIM() function does not do that.

    If you want to get rid of additional spaces in between words as well, i suggest you call the Excel TRIM function in VBA by ising Application.worksheetfunction.trim(rng.value)

    2 people found this answer helpful.
    0 comments
  3. Anonymous
    2012-06-06T11:41:06+00:00

    Thank you all for replying to my question and I am sorry for the ambiguity in my original question.   I hope this better defines my issue.  I have five columns of data on a spread sheet, columns A thru E.   I am looking to trim all Leading and Trailing space that may be in each cell.  On the rare occation that there may be a space located between words I would like to leave that space there.   Below are a few rows from my spread sheet.

    757-3A-SS25 011 10 P-000757-S325R 1
    757-3A-SS25 011 20 P-041634-D743 0
    P-000757-S325R 012 1 FCF604 0
    P-000757-S325R 012 10 362121 0

    I am learning VBA on my own and I am having trouble getting the Applications.Worksheetfunction.trim(rng.value) to work.  Now that I take a closer look at the code Jim provided I think I may see what I am doing wrong with Applications.Worksheetfunction.trim(rng.value).

    I have tried using the code Jim provided and it does exactly what I need.

    Thank you.

    Once again you folks have been able to help me with my issue.  You are a great resource.  I am glad you are around to help.

    1 person found this answer helpful.
    0 comments
  4. Anonymous
    2012-06-06T00:07:28+00:00

    Ashish wrote:

    If you want to get rid of additional spaces in between words as well, i suggest you call the Excel TRIM function in VBA by ising Application.worksheetfunction.trim(rng.value)

    I was going to write a similar comment.  But I decided it might be best to let Jim answer any follow-up questions, especially since the OP's question is ambiguous -- and in general, it is not unusual for OPs to say one thing, but mean another.  Jim might have hit the nail on the head by accident.  Or we all might be misunderstanding the OP's intention completely.

    In fact, it is possible that neither the Excel TRIM function nor the VBA Trim function does what the OP requires.

    "Raiders_Fan" wrote that he/she wants to "remove any spaces".

    As Ashish points out, the VBA Trim function will leave all interstitial spaces.

    But the Excel TRIM function does not remove all interstitial spaces either.  Instead, it collapses each sequence of interstitial spaces to one space.

    0 comments