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

by

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!

enter image description here

Sub Test()

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

End Sub

enter image description here

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 =)

More Related Contents:

  1. SumIf using Excel VBA
  2. How can I call a macro which contains a loop but only run one iteration of that loop?
  3. Test or check if sheet exists
  4. How can I find last row that contains data in a specific column?
  5. Copy from one workbook and paste into another
  6. How to copy Outlook mail message into excel using VBA or Macros
  7. Open Excel file for reading with VBA without display
  8. For Loop not fully cycling in excel VBA [duplicate]
  9. Unique Random Numbers using VBA
  10. Populate unique values into a VBA array from Excel
  11. Excel VBA App stops spontaneously with message “Code execution has been halted”
  12. How to remove all non alphanumeric characters from a string except period and space in excel?
  13. Does Dir() make any guarantee on the order of files returned?
  14. Closing Excel Application using VBA
  15. How to clear memory to prevent “out of memory error” in excel vba?
  16. Assign on-click VBA function to a dynamically created button on Excel Userform
  17. What are the benefits of setting objects to “Nothing”
  18. Using a wildcard to open an excel workbook
  19. How can I kill task manager processes through VBA code?
  20. Extract Data from Word Document to an Excel SpreadSheet
  21. Excel macro -Split comma separated entries to new rows [duplicate]
  22. VBA equivalent to Excel’s mod function
  23. Get text from clipboard using GetText – avoid error on empty clipboard
  24. Pointers needed for speeding up nested loop macro in VBA
  25. VBA editor auto-deletes spaces at the ends of lines
  26. ‘1004’: “The sort reference is not valid.”
  27. Select entire column in table using Excel VBA
  28. Reducing WithEvent declarations and subs with VBA and ActiveX
  29. Function Overloading and UDF in Excel VBA
  30. VBA: Modify chart data range

Leave a Comment