Trim Column in VBA

[Blister]

Hello,

I'm looking for a way apply the Trim function to a column ranging from cell 2 to the last active cell in the column. In this case the data starts at E2.

 

[footoo]

By comparison, the loop code suggested earlier by DushiPunda was taking so long that I stopped it and measured what had taken place up to then... after 7.5 minutes, the loop had processed only 91,357 rows of the 200,000 that were there...

@Rick Rothstein
Maybe time for an upgrade.
I tested on 200,000 rows and it took only 7 seconds.

 

[footoo]

Since DushiPunda's code satisfied the OP, the number of rows were less than 32,768.

For 20,000 rows, DushiPunda's code took 0.828 seconds on my computer - so as mentioned in post #9 : “Normally, the only reason to avoid a worksheet object loop is to reduce run-time - any such reduction is often immaterial.” As in this case.

 

[zookeepertx]

For those who might read this thread in the future... DushiPunda's solution will only trim outside space. If you want to apply Excel's TRIM function which does that plus it collapses multiple adjacent internal spaces down to single spaces, then you can use this macro...

EXACTLY what I needed to get my macro that was ALMOST running right to ACTUALLY run right! Thank you so much!

Jenny

 

[pmich]

@Rick Rothstein
Your Evaluate Code has removed leading & Trailing Space and also has removed extra spaces in between words in the column, which has less number of rows. I wanted this instead of looping. Thanks.