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

 

[chirp]

hey Jon

why did you use row()? wont that always evaluate true?

 

[Jon von der Heyden]

I struggle with the logic as to why this works too, so perhaps someone else who knows can add to this topic. Best way for me to explain is to study a small sample:

Enter some random text in A1:A3

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">   salsag  dshklsau   khsa sdkl   hsadhk; </td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">   kljsd sh hsd;kh sdkhsa ;ksahs da; ;   sad I;hhids h;    sdih</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">  dhids I;h sd ;    sdihi sdsdh     hkds usdui </td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet2</p>

Now grab all of A5:A7 and type:
=TRIM($A$1:$A$3)
And commit with CONTROL+ENTER

The formula result will be #VALUE!. This is because we have fed TRIM an array of values and it is intended to trim only a single text element.

The use of the IF(ROW() when used with the evaluate is a tip that appears to allow us to process and entire range in one hit, yet the function will not only handle each individual string element and not yield an error.

In worksheet level we can achieve the same with an array formula, to return an array of values:

Grab all of A5:A7 and type:
=TRIM(A1:A3)
And commit with CONTROL+SHIFT+ENTER

But the evaluate method doesn't work the same way as array entered array formulae...