Clean Function to Clean Entire Sheet

[gema]

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:

Please Login or Register  to view this content.

Any ideas? Thanks

[ncmay]

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

[modytrane]

try;

Please Login or Register  to view this content.

modytrane

[shg]

Modytrane, the operative word was Clean, not Clear.

[gema]

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.

[modytrane]

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

[DonkeyOte]

Ivan Moala is a genius (IMO) so I'd say his code is good and you are let's face it running code on over 1.3 million cells so it's never going to be quick!

You could toggle the App level settings and see if that helps... (screenupdate etc...), also on an aside the code utilises SpecialCells so as to restrict number of cells being iterated - given the vast range I suspect you *may* need to iterate your range in blocks given there is a limit on how many non-contiguous ranges SpecialCells can handle (resolved in 2010 apparently).

You could also contemplate running an Evaluate call rather than Iteration, however, again given volume of cells I have little idea as to the performance impact that approach would have!
(EDIT: and Evaluate probably not viable if you have lengthy strings)

[gema]

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:

Please Login or Register  to view this content.

I guess it's not that simple.