TRIM in VBA on a Range

ilcaa

Well-known Member
Joined
May 25, 2005
Messages
751
Office Version
  1. 365
Platform
  1. Windows
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

<code>

set rng = range("a1:a6000")

With rng

.value = WorksheetFunction.Trim(.value)

End With


</code>
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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"
 
Upvote 0
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.
 
Upvote 0
Just for fun, this also can be used:
Rich (BB code):
Sub TrimRange()
  With Range("A1:A6000")
    .Value = Application.Trim(.Value)
  End With
End Sub
:)
 
Upvote 0
Just for fun, this also can be used:
Rich (BB 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 :confused:
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top