How to apply CLEAN func.

[Oded Dror]

Hi there,

How to creates macro that use the clean function for a selection in a
spreadsheet ?

Also how to remove just the ? mark from a selection in a spreadsheet?

I have a spreadsheet the hold names with ? at the and of a cell and I wanted
to remove it without removing the name.

Thanks an advanced

Oded Dror
Email: (e-mail address removed)

 

[Dave Peterson]

#1.

Option Explicit
Sub testme()

Dim myRng As Range
Dim myCell As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells _
.SpecialCells(xlCellTypeConstants, xlTextValues))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "nothing to clean in that selection"
Exit Sub
End If

For Each myCell In myRng.Cells
myCell.Value = Application.Clean(myCell.Value)
Next myCell

End Sub

#2. Select your range and edit|replace. But use ~? (tilde question mark) in
the Find What box.

The question mark is a wild card. The tilde tells excel that you really want a
question mark.

(same technique for both the asterisk and tilde (~* and ~~).)