[Gareth - Network analyst.]

Hi guys/Girls

Is there a way to apply the CLEAN function to an entire worksheet?

[Bob Phillips]

Public Sub Test()
Dim cell As Range

For Each cell In ActiveSheet.UsedRange
If Not cell.HasFormula Then
cell.Value = Application.Clean(cell.Value)
End If
Next cell
End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Gareth - Network analyst."
wrote in message ...
Hi guys/Girls

Is there a way to apply the CLEAN function to an entire worksheet?

[Gary''s Student]

Select the cells you want to clean and run:

Sub doit()
For Each r In Selection
If Application.WorksheetFunction.IsText(r) Then
r.Value = Application.WorksheetFunction.Clean(r.Value)
End If
Next
End Sub
--
Gary''s Student - gsnu200753


"Gareth - Network analyst." wrote:

Hi guys/Girls

Is there a way to apply the CLEAN function to an entire worksheet?