How can I delete unprintable characters using VBA in Excel?

[peterv]

I want to delete unprintable characters in an Excel sheet using VBA. I've tried using the CLEAN function, but can't find the correct syntax to get it to work in VBA. Can anyone help?
Thanks!

[K Dales]

Use WorksheetFunction.Clean(YourString) - for example, if you wanted to clean
the text in cell A3 you would have:
Range("A3").Value = WorksheetFunction.Clean(Range("A3").Value)
--
- K Dales


"peterv" wrote:

>
> I want to delete unprintable characters in an Excel sheet using VBA.
> I've tried using the CLEAN function, but can't find the correct syntax
> to get it to work in VBA. Can anyone help?
> Thanks!
>
>
> --
> peterv
> ------------------------------------------------------------------------
> peterv's Profile: http://www.excelforum.com/member.php...o&userid=25572
> View this thread: http://www.excelforum.com/showthread...hreadid=471435
>
>

[Tom Ogilvy]

for each cell in Activesheet.UsedRange
cell.Value = Application.Clean(cell.Value)
Next


Just note that if you are bringing in your data from a web page (for
example), there may be characters that CLEAN doesn't clean.

--
Regards,
Tom Ogilvy

"peterv" <[email protected]> wrote in
message news:[email protected]...
>
> I want to delete unprintable characters in an Excel sheet using VBA.
> I've tried using the CLEAN function, but can't find the correct syntax
> to get it to work in VBA. Can anyone help?
> Thanks!
>
>
> --
> peterv
> ------------------------------------------------------------------------
> peterv's Profile:
http://www.excelforum.com/member.php...o&userid=25572
> View this thread: http://www.excelforum.com/showthread...hreadid=471435
>

[Gord Dibben]

See David McRitchie's TrimAll code.

http://www.mvps.org/dmcritchie/excel/join.htm#trimall


Gord Dibben Excel MVP


On Wed, 28 Sep 2005 10:18:17 -0500, peterv
<[email protected]> wrote:

>
>I want to delete unprintable characters in an Excel sheet using VBA.
>I've tried using the CLEAN function, but can't find the correct syntax
>to get it to work in VBA. Can anyone help?
>Thanks!