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

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

--
Don Guillett
SalesAid Software

"JJ" wrote in message
...
Is there a way I can apply the clean function to my whole spreadsheet?

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

--
crispb
-----------------------------------------------------------------------
crispbd's Profile: http://www.excelforum.com/member.php...fo&userid=1088
View this thread: http://www.excelforum.com/showthread.php?threadid=27454

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

--
Don Guillett
SalesAid Software

"crispbd" wrote in message
...

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

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

ActiveSheet.Cells.Select
Selection.ClearContents

Application.ScreenUpdating=True
End Sub


--
crispbd
------------------------------------------------------------------------
crispbd's Profile:
http://www.excelforum.com/member.php...o&userid=10880
View this thread: http://www.excelforum.com/showthread...hreadid=274548

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
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"JJ" wrote ...
Is there a way I can apply the clean function to my whole spreadsheet?

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?

"David McRitchie" wrote:

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
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"JJ" wrote ...
Is there a way I can apply the clean function to my whole spreadsheet?

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

--
Regards,
Tom Ogilvy

"JJ" wrote in message
...
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?

"David McRitchie" wrote:

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
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"JJ" wrote ...
Is there a way I can apply the clean function to my whole spreadsheet?