Deleting spaces in cells

Question

I have a VBA code sample that I use on a table to delete spaces in cells. I want to ask if someone has a better and quicker way to the Trim function on a table than what I have:

This is the sample:

If Sheets("Main").Cells(8, 6) = 1 Then
 strName = Sheets("Main").Cells(8, 3)
 Workbooks.Open Filename:="V:\á÷øåú ùëø\ãåçåú\ãåçåú çæåúééí\ãåçåú ìäîøä\exhr0101.csv"
 Columns(7).Delete
 Cells.Select
 Selection.Copy
 Windows("Trim all cells.xlsm").Activate
 Sheets("Data").Select
 Cells.Select
 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
 :=False, Transpose:=False
 'ñâéøú çæåúé î÷åø
 Windows("exhr0101.csv").Activate
 Application.DisplayAlerts = False
 ActiveWindow.Close
 Application.DisplayAlerts = True
 'áéöåò Trim
 Windows("Trim all cells.xlsm").Activate
 Sheets("Data").Select
 For Each cell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)
 cell = WorksheetFunction.Trim(cell)
 Next cell
 'äãá÷ä áâéìéåï äãá÷ä
 Cells.Select
 Selection.Copy
 Sheets("Target").Select
 Cells.Select
 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
 :=False, Transpose:=False

Here is my specific trim:

For Each cell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)
cell = WorksheetFunction.Trim(cell)
Next cell

• QHarr: For a start is there a typed function Trim$ ?
• Gary's Student: Application.Worksheetfunction.Trim() will leave a single space between words in a cell. Is this what you want??
• Rafael Osipov: @QHarr what do you mean
• Rafael Osipov: @Gary'sStudent yes. I know what trim does. I want it to do it quicker
• QHarr: Can you replace Trim with Trim$ without raising an error or it being swopped back to Trim. I can’t remember.

Answer 1

You can avoid loop altogether.

 With Activesheet.UsedRange
 .Value = Application.Trim(.Value)
 End With

Warning: will give error if any cell has more than 255 characters.

Answer 2

You don't need to have the data in a specific worksheet to use Trim, or any macro for that matter. It looks like you are copying data from your worksheet to "Trim all cells.xlsm." In reality, if you just have the workbook open and make sure the active cell is on the workbook you want to trim, you should be good.

Also, setting Application.ScreenUpdating to false before you run the code will speed it up dramatically. Just make sure you set it back to true at the end of the code.