Vba - trim

[Nanogirl21]

I am trying to use VBA to trim and clean up all data on a worksheet. I would like to remove leading, trailing, and any extra intral spaces. Below is the code that I am trying to use, but it will not work. I am using cells.select to select all data on the worksheet. If I remove that line and select 1 individual cell the code will work only for the 1 cell. I want to use this for all cells on worksheet. I do not have a specific range becuase it will always change.

    Cells.Select
With Selection
    .Value = Application.WorksheetFunction.Trim(Application.WorksheetFunction.Clean(Replace(.Value, Chr(160), " ")))
End With

 

[Fluff]

How about

Sub Nanogirl()
   With ActiveSheet.UsedRange
      .Value = Evaluate("if({1},trim(clean(substitute(" & .Address & ",char(160),"" ""))))")
   End With
End Sub