Speed Up Trim Macro

[Dazzawm]

I was given the code below to run a Trim Macro. I have 10000 rows and 30 columns of data for it check and it takes forever and sometimes freezes. Is there anyway it can be speeded up please?

Sub trimCells()
Application.ScreenUpdating = False
  For Each c In ActiveSheet.UsedRange
    c.Value = WorksheetFunction.Trim(c.Value)
  Next c
Application.ScreenUpdating = True
End Sub

 

[Jon von der Heyden]

This should be quicker:

Public Sub TrimIt()
    With ActiveSheet.UsedRange
        .Value = Evaluate("if(row(),trim(" & .Address & "))")
    End With
End Sub

 

[Jon von der Heyden]

Beware though because this will convert formulas to constants...

 

[Dazzawm]

Will this remove extra spaces between words etc.?

 

[Jon von der Heyden]

Yes, multiple spaces will be reduced to just one space. Trailing and leading spaces will be removed entirely.

 

[Dazzawm]

Beware though because this will convert formulas to constants...

So this means its just like copy - paste special - values?

 

[Dazzawm]

Yes, multiple spaces will be reduced to just one space. Trailing and leading spaces will be removed entirely.

Thanks, exactly what I want.

 

[JackDanIce]

Try:

Sub EatinMePenguin()
 
Dim x As Long, y As Long, a As Long, b As Long
 
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
 
y = Cells.Find(what:="*", after:=Range("A1"), searchorder:=xlByColumns, searchdirection:=xlPrevious).Column
x = Cells.Find(what:="*", after:=Range("A1"), searchorder:=xlByRows, searchdirection:=xlPrevious).Row
 
For a = 1 To x
    For b = 1 To y
        If Len(Cells(a, b)) > 0 Then Cells(a, b).Value = Trim(Cells(a, b))
    Next b
Next a
 
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
 
End Sub

 

[Jon von der Heyden]

Not quite a copy and paste. The evaluate method evaluates a formula yet returns a constant. This method allows us to handle the entire used range in one hit.

 

[happypig]

Thanks Jon, I used the function on over 1.8M cells, and it only took 13-14 seconds on a Core i7 processor.