TRIM in VBA on a Range

[ilcaa]

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>

 

[Akuini]

Try:

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

 

[JoeMo]

With 6000 cells, this non-looping solution may be faster:

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

 

[ilcaa]

With 6000 cells, this non-looping solution may be faster:

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

 

[JoeMo]

this worked perfectly. thanks

You are welcome - thanks for the reply.

 

[ilcaa]

With 6000 cells, this non-looping solution may be faster:

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

 

[alansidman]

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

Here is the Mcode for that

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"

 

[JoeMo]

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.

 

[ZVI]

Just for fun, this also can be used:

Sub TrimRange()
  With Range("A1:A6000")
    .Value = Application.Trim(.Value)
  End With
End Sub

 

[JoeMo]

Just for fun, this also can be used:

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