Clean funtion

[Guest]

Is there a way I can apply the clean function to my whole spreadsheet?

 

[Don Guillett]

I can't remember it there is a vba equivalent so try this

sub cleanitup()
for each cel in selection
cel.value=application.clean(cel)
next
end sub

 

[crispbd]

If you would like to clean an entire sheet of its contents (withou
removing formatting) use the ClearContents method, otherwise use th
delete method. Here is an example:

Sub ClearActiveSheet ()
Application.ScreenUpdating=False ' lets code execute faster

ActiveSheet.Cells.Select
Selection.ClearContents

Application.ScreenUpdating=True
End Su

 

[Don Guillett]

JJ said cleaN not cleaR. There is a difference.
BTW. you do NOT need to select and stopping updating wouldn't make this
faster.

ActiveSheet.UsedRange.ClearContents

 

[David McRitchie]

are you sure you want to use CLEAN.

The use of the CLEAN Worksheet Function to remove unprintable characters
will only remove CHAR(0) through CHAR(31), Char(129), Char(141), Char(143),
and Char(144). It will have no effect on Char(160).

When you apply the function to all cells in a workbook you would wipe out
formulas; remove linebreaks closing up words that won't even have a space
between them.

Suggest reading the paragraphs before and within the TRIMALL macro
description on my page.
http://www.mvps.org/dmcritchie/excel/join.htm#trimall

 

[Guest]

I want to remove ANY characters there are not alphanumeric (not to include
spaces or hyphens, I still need those). These cells will not have formulas
at the time I'm cleaning up the cells. Since I pull from different
databases, I run into a problem when I compare the cell values - which I need
to do from the right. If there is a character that takes a space they won't
match and because this character is not appearing in every cell or even in
every column I can't just say ignore the first right character.

Does that make sense?

 

[Tom Ogilvy]

Sub CleanCells()
Dim sStr as String
Dim cell as Range
for each cell in selection
if not cell.hasFormla then
sStr = Application.Substitute(cell.value,chr(160)," ")
sStr = Application.Clean(sStr)
sStr = Application.Trim(sStr)
cell.Value = sStr
end if
Next
End Sub