Clean the UsedRange

What does your CleanString function look like?

Here is how I would do the above code:

Please Login or Register  to view this content.

hi jomili, two questions:
1. Why do you use Select in your code?
2. The flickering comes from looping the cells I guess. You could possibly try to use Specialcells (data volume is unknown)

You could also try this:

Please Login or Register  to view this content.

Roy,
I was working on the same lines as you, prompted by Watersev's suggestion. Here's my crude attempt:

Please Login or Register  to view this content.

Yours looks a lot cleaner. Why did you use xlCellTypeConstants? I would have thought Visible would work...never mind, it just clicked! Using constants I don't have to have my check for HasFormula. Duh! You make it look so easy!

Thanks to everyone for all the help on this one. I think you've all improved my coding loads. I appreciate all your efforts.

Thanks, and Happy New Year,
John

Roy,

I just noticed your code uses "ClearContents", not "CleanString". How do we adapt it? I tried

Please Login or Register  to view this content.

but that resulted in an error.

Try:

Please Login or Register  to view this content.

Thanks Whizbang; that does the trick! Thanks so much for the followthrough.

Happy New Year!