Page 2 of 2 FirstFirst 12
Results 11 to 15 of 15

Thread: TRIM in VBA on a Range
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,241
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: TRIM in VBA on a Range

    Quote Originally Posted by ZVI View Post
    Just for fun, this also can be used:
    Code:
    Sub TrimRange()
      With Range("A1:A6000")
        .Value = Application.Trim(.Value)
      End With
    End Sub
    And, since the range is fixed in size, either of these one-liners will also work...
    Code:
    Sub TrimRange()
      [A1:A6000] = [IF({1},Trim(A1:A6000))]
    End Sub
    Code:
    Sub TrimRange()
      [A1:A6000] = Application.Trim([A1:A6000])
    End Sub
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  2. #12
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,148
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: TRIM in VBA on a Range

    Or, for anyone using Office 365
    Code:
    Sub TrimRange()
      [A1:A6000] = [Trim(A1:A6000)]
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #13
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,241
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: TRIM in VBA on a Range

    Quote Originally Posted by Fluff View Post
    Or, for anyone using Office 365
    Code:
    Sub TrimRange()
      [A1:A6000] = [Trim(A1:A6000)]
    End Sub
    Office 365 does not require the array processing to be induced into text functions? When I run your code line in XL2010, it clears all of the cells which is why I used the IF function call that I did in my first macro (Message #11 ) in order to induce the array processing.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  4. #14
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,148
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: TRIM in VBA on a Range

    Whilst I still use the If function, as most people are not using 365, it seems that it's no longer needed. I've tried it with various different formulae & it works without the If.
    I'm guessing that It's got something to do with the new array functions, even though I don't have them yet.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #15
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,241
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: TRIM in VBA on a Range

    Quote Originally Posted by Fluff View Post
    Whilst I still use the If function, as most people are not using 365, it seems that it's no longer needed. I've tried it with various different formulae & it works without the If.
    I'm guessing that It's got something to do with the new array functions, even though I don't have them yet.
    Interesting... well atleast Microsoft is maintaining backward compatibility.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •