Speeding up this once speedy code

[Grizlore]

Could anyone suggest how I can speed up this code please?

This used to work really quickly, however on my new quicker 'laptop' it takes ages.

I can see in the bottom Excel bar it displaying Calculating (4 processers: x%). Which I have never seen before!

Anyway.. here is the code

Sub TrimEVERYTHING()

Dim rng, rngC, rngUnion As Range
 
Set rng = ActiveSheet.UsedRange
Set rngUnion = Nothing
 
For Each rngC In rng
    If Left(rngC.Formula, 1) <> "=" Then
        If rngUnion Is Nothing Then
            Set rngUnion = rngC
        Else
            Set rngUnion = Union(rngUnion, rngC)
        End If
    End If
Next rngC
 
For Each rngC In rngUnion
    rngC.Value = Evaluate("IF(ROW(" & rngC.Address & "),IF(COLUMN(" & rngC.Address & "),TRIM(" & rngC.Address & ")))")
Next rngC
 
MsgBox "TRIMMED"
End Sub

Any help in making this do exactly the same job, but be quicker would be appreciated

 

[VoG]

Try this

Sub TrimEVERYTHING()

Dim rng As Range, rngC As Range, rngUnion As Range
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
Set rng = ActiveSheet.UsedRange
Set rngUnion = Nothing
 
For Each rngC In rng
    If Left(rngC.Formula, 1) <> "=" Then
        If rngUnion Is Nothing Then
            Set rngUnion = rngC
        Else
            Set rngUnion = Union(rngUnion, rngC)
        End If
    End If
Next rngC
 
For Each rngC In rngUnion
    rngC.Value = Evaluate("IF(ROW(" & rngC.Address & "),IF(COLUMN(" & rngC.Address & "),TRIM(" & rngC.Address & ")))")
Next rngC

With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
 
MsgBox "TRIMMED"
End Sub

 

[Grizlore]

Thanks, now it is lightening fast

I see the changes, so... .Calculation = xlCalculationManual
was cycling through and slowing it all down?

It is the first time I have seen this.

Thanks again!

Much Appreciated !