Page 1 of 2 12 LastLast
Results 1 to 10 of 15

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

  1. #1
    Board Regular
    Join Date
    May 2005
    Posts
    686
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default TRIM in VBA on a Range

    i have a range with 6000 rows, i want to apply a worksheet.TRIM function within VBA to eliminate spaces in-between and on the ends...

    problem is... nothing happens. whats best syntax for this? thanks



    set rng = range("a1:a6000")

    With rng

    .value = WorksheetFunction.Trim(.value)

    End With



  2. #2
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,042
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    4 Thread(s)

    Default Re: TRIM in VBA on a Range

    Try:
    Code:
    Dim c As Range
    Application.ScreenUpdating = False
        For Each c In Range("A1:A6000")
        c.Value = WorksheetFunction.Trim(c.Value)
        Next
    Application.ScreenUpdating = True

  3. #3
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,453
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    8 Thread(s)

    Default Re: TRIM in VBA on a Range

    With 6000 cells, this non-looping solution may be faster:
    Code:
    Sub TrimRng()
    Dim rng As Range
    Set rng = Range("A1:A6000")
    Application.ScreenUpdating = False
    With rng
        .Value = Evaluate(Replace("If(@="""","""",Trim(@))", "@", .Address))
    End With
    Application.ScreenUpdating = True
    End Sub
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  4. #4
    Board Regular
    Join Date
    May 2005
    Posts
    686
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: TRIM in VBA on a Range

    Quote Originally Posted by JoeMo View Post
    With 6000 cells, this non-looping solution may be faster:
    Code:
    Sub TrimRng()
    Dim rng As Range
    Set rng = Range("A1:A6000")
    Application.ScreenUpdating = False
    With rng
        .Value = Evaluate(Replace("If(@="""","""",Trim(@))", "@", .Address))
    End With
    Application.ScreenUpdating = True
    End Sub

    this worked perfectly. thanks

  5. #5
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,453
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    8 Thread(s)

    Default Re: TRIM in VBA on a Range

    Quote Originally Posted by ilcaa View Post
    this worked perfectly. thanks
    You are welcome - thanks for the reply.
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  6. #6
    Board Regular
    Join Date
    May 2005
    Posts
    686
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: TRIM in VBA on a Range

    Quote Originally Posted by JoeMo View Post
    With 6000 cells, this non-looping solution may be faster:
    Code:
    Sub TrimRng()
    Dim rng As Range
    Set rng = Range("A1:A6000")
    Application.ScreenUpdating = False
    With rng
        .Value = Evaluate(Replace("If(@="""","""",Trim(@))", "@", .Address))
    End With
    Application.ScreenUpdating = True
    End Sub
    Hi Joe, i was wondering if you can explain this syntax. i have tried to read up on the EVALUATE funtion but cant anything on applying the syntax. You have @ and quotes that i cant wrap my head around. if you have time can you break this down for me?

    thanks

  7. #7
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,929
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: TRIM in VBA on a Range

    An alternative to VBA is to employ Power Query/get and transform

    Here is the Mcode for that
    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Trimmed Text" = Table.TransformColumns(Table.TransformColumnTypes(Source, {{"Column1", type text}}, "en-US"),{{"Column1", Text.Trim, type text}})
    in
        #"Trimmed Text"
    Let me know if that works for you
    Alan

    Am Yisrael Chai

    Win 10--Office 2019
    When Posting Code, please use code tags.
    How to insert Mcode to Power Query https://excel.solutions/2017/11/powe...te-code-video/


  8. #8
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,453
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    8 Thread(s)

    Default Re: TRIM in VBA on a Range

    Quote Originally Posted by ilcaa View Post
    Hi Joe, i was wondering if you can explain this syntax. i have tried to read up on the EVALUATE funtion but cant anything on applying the syntax. You have @ and quotes that i cant wrap my head around. if you have time can you break this down for me?

    thanks
    The Evaluate function uses worksheet functions so the Trim will remove not only leading and trailing spaces like the VBA Trim function, but also internal extra spaces, unlike the VBA Trim. The @ is simply a placeholder, you could use anything in its place, that gets replaced by the cell address via the VBA Replace function. So the evaluation first looks to see if the cell is empty (in VBA parlance that's cell value of """"), if not, the cell is trimmed.
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  9. #9
    MrExcel MVP ZVI's Avatar
    Join Date
    Apr 2008
    Location
    Sevastopol
    Posts
    3,626
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    3 Thread(s)

    Default Re: TRIM in VBA on a Range

    Just for fun, this also can be used:
    Code:
    Sub TrimRange()
      With Range("A1:A6000")
        .Value = Application.Trim(.Value)
      End With
    End Sub
    Vladimir Zakharov

  10. #10
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,453
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    8 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
    Fun and simpler too. Don't know why, but I stayed away from this because I thought it would generate a run time error
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

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
  •