VBA to TRIM(CLEAN(All Cells in Used Range))

[Dennis]

Using XL 97 & 2003

Help re: writting the most efficient VBA code to
1) select all cells in "Used Range" (got that I think)
2) Replace each cell in that used range with
Trim(Clean(each cell)) [Not so easy]

Thoughts?

TIA Dennis

 

[Dennis]

Excellent!

The answer:

Sub TrimCleanCells()
' Trims & Cleans all constants (does not effect formula
cells)
'
'
Dim Rng As Range, myCell As Range
Set Rng = ActiveSheet.UsedRange.SpecialCells
(xlCellTypeConstants, 23)
For Each myCell In Rng
myCell.Value = WorksheetFunction.Trim
(WorksheetFunction.Clean(myCell.Value))
Next
End Sub