Clean Function to Clean Entire Sheet

Hello,

I have data that has some unreadable (comes up as a box) characters. The clean function within Excel gets rid of the characters perfectly. I would like to create a macro that will clean all of the data on the sheet. I currently have data in cells A1:JD5000.

I can use the following code to get rid of the bad characters in individual cells, but I cannot figure out how to modify it to clean the whole worksheet:

Range("A1").Value = Application.WorksheetFunction.Clean(Range("A1"))

Any ideas? Thanks

See if this is what you are looking for?
http://www.xcelfiles.com/VBA_Quick10.html

Originally Posted by ncmay

See if this is what you are looking for?
http://www.xcelfiles.com/VBA_Quick10.html

That does work, but it is really slow. Since there are a ton of cells that need cleaned, I could see this taking a really long time. Thanks for the link though.

sorry about that.
I need new glasses or bigger screen.
modytrane

try;

Cells.Select
    Selection.Clear

modytrane

Prior to trying to produce my own code, I recorded a macro that was doing the job in about 20-30 seconds. I simply inserted a column next to any column that had the unwanted characters, used the clean function and copied it down, copy and pasted values, then deleted the old column.

I was hoping I could just rewrite the code I posted above to something like this:

Range("List the columns I need cleaned here").Value = Application.WorksheetFunction.Clean(Range("List the columns I need cleaned here"))

I guess it's not that simple.