Faster way to remove 'extra' spaces (more than 1) from a large range of cells using VBA for Excel

Question

How do I remove extra spaces faster, from a large range of cells containing text strings?

Let's say 5000+ cells.

Some ways I have tried include:

For Each c In range
    c.Value = Trim(c.Value)
Next c

and

For Each c In range
    c = WorksheetFunction.Trim(c)
Next c

and

For Each c In range
    c.Value = Replace(c.Value, "     ", " ")
Next c

Any ideas for speed improvement?

Answer 1

Late to the party but...

There is no need for iteration through cells/values nor a recursive function to search and replace multiple spaces in a range.

Application.Trim wil actually take care of multiple spaces between words (and will trim leading/trailing spaces) leaving single spaces in between words intact.

The great thing about it, is that you can feed the function a full range (or array) to do this operation in one sweep!

---

Sub Test()

Dim rng As Range
Set rng = Sheets("Sheet1").Range("A1:A3")
rng.Value = Application.Trim(rng)

End Sub

---

The one thing to take into consideration is that this way you'll overwrite any formulas sitting in your target range with its value. But as per your question, you working with a Range object containing text values. There was just no need for iteration =)