Efficient way to trim data

[ThrottleWorks]

Hi,

I am using below code to trim a range. However this code is taking time.
Can anyone please suggest me a better code to trim data.

Thanks.

Sub Trim_Raw_Data()
    Dim RawSht As Worksheet
    Dim TempLr As Long
    Dim TempRng As Range
    Dim TRng As Range
    Dim LastCol As Long
   
    LastCol = 17 'Q
    Set RawSht = ActiveSheet
    TempLr = RawSht.Cells(RawSht.Rows.Count, 1).End(xlUp).Row
    Set TempRng = RawSht.Range(RawSht.Cells(1, 1), RawSht.Cells(TempLr, LastCol))
   
    For Each TRng In TempRng
        TRng = Trim(TRng)
    Next TRng
End Sub

 

[vletm]

ThrottleWorks
1) The quickest: Get Data trimmed
2) Add in the beginning:
Application.ScreenUpdating = false
Application.Calculation =xlCalculationManual
and
in the end:
revert those

 

[shrivallabha]

You can try following as well.

In place of

For Each TRng In TempRng
    TRng = Trim(TRng)
Next TRng

you can use:

TempRng.Value = Application.Trim(TempRng.Value)

Note: there's a limitation of string length(256) when Application.Trim is used.

 

[ThrottleWorks]

Hi @vletm sir, thanks for the help. I am using screen updating.
However was not using manual calculation mode. Will try going forward.

Have a nice day ahead.

 

[ThrottleWorks]

Hi @shrivallabha sir, how are you ? thanks for the help.
I will check this. Have a nice day ahead.

 

[Marc L]

Hi !

As always without any loop but just using a worksheet formula,
Excel basics …
​