Instead of looping through each cell, you could do the equivalent of
edit|replace:

Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim myGoodChars As Variant
Dim iCtr As Long
dim Wks as worksheet

myBadChars = Array(Chr(160))

myGoodChars = Array(" ")

If UBound(myGoodChars) < UBound(myBadChars) Then
MsgBox "Design error!"
Exit Sub
End If

for each wks in activeworkbook.worksheets
For iCtr = LBound(myBadChars) To UBound(myBadChars)
ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _
Replacement:=myGoodChars(iCtr), _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
Next iCtr
next wks

End Sub

This was left over from a previous post. I left it as-is (using arrays). I
thought that it would be useful if you decide you wanted to replace other
characters, too.



Curt wrote:

I would like a VBA that replaces every cell of every sheet in a workbook with
its output when used with the following function:

=TRIM(SUBSTITUTE(A1,CHAR(160),CHAR(32)))

thanks

Curt J

--

Dave Peterson