Replace all cells with len()=0 to empty strings

[BNCOXUK]

I made a macro below to replace all cells with len()=0 in the sheet 'ws' to empty strings:

Please Login or Register  to view this content.

I noticed that this macro is very slow as the sheet contains nearly 50k rows. Is there a more efficient way?

[XLent]

perhaps something like the below, the null string (FALSE) should be treated as a blank when written back to the worksheet (I.e. COUNTA reverts from 1 to 0)

Please Login or Register  to view this content.

[BNCOXUK]

Many thanks, XLent. Looks very advanced.

[TMS]

This should be quicker:

Please Login or Register  to view this content.

[Artik]

Please Login or Register  to view this content.

Note that typing an empty string is not the same as clearing a cell. Rather, it should be

Please Login or Register  to view this content.

Visually, it's kind of the same, but there are situations where Excel is able to see the difference between an empty cell (by ClearContents) and a cell with an empty string typed. And I'm not talking about the fairly common case where after copying formulas that returned empty string and pasting as values, we get a "full" cell, not "empty".

Artik

[TMS]

If you type ="" you get a zero length cell value with a formula in it. That's what the code is looking for, and converts it to an empty cell. I couldn't see any difference compared to .ClearContents.

[Artik]

You may not see, but believe me, Excel can see the difference in some cases.
OK, maybe I overdid it. But ADO sees the difference.

Artik