WITH statement possible?

[nbrcrunch]

just for kicks, I tried entering this from scratch, not even knowing if it would work. Though I later discovered that formal coding requires a little more, nonetheless, the code works.

My question is: I've read here a few times that if at all possible, avoid loops. Is there a way to achieve the end-result (trimming text values) without looping? Possibly using a WITH (object / feature) type of syntax?

Sub trimall()
For Each cell In Selection
cell.Value = Trim(cell.Value)
Next cell
End Sub

 

[BrianB]

With faster computers nowadays this is not really an issue. Trying to change code such as yours to save a millisecond or two would be more trouble than it is worth.

This might change a bit if we have to apply that to thousands of records in a database. I never bother, even though I often have to process over 1 million records with code in Access. Putting a counter in the statusbar shows how blindingly fast things are happening, even using our outdated equipment.

You can save more run time by adding a couple of lines of code to set Calculation to Manual and Screenupdating to False and back again.

Having said that, using Copy & Paste in code rather than the equivalent loops does make a difference in running those thousands of records - but then most people would not bother with loops anyway. In this case the loops are still there, but contained in Excel code.