unpopulated blank cells

[wmtsub]

Sometimes when working with what looks like cells that look blank they have hidden "0" in it.
That messes with formulas at times. So I thought to scrub the worksheet for cells with non visible characters and set them all to the same one.
So what I like to know is what state is a new unused cell in IE> blank - zero - other. And how to search for and change everything that is not
in use to that. Kind of like below?




Sub Trim_Zero()
'Trim zeros
ActiveSheet.UsedRange.Replace 0, "", lookat:=xlWhole
ActiveSheet.UsedRange.Replace " ", "", lookat:=xlWhole
End Sub

 

[Joe4]

Sometimes when working with what looks like cells that look blank they have hidden "0" in it.

If they have a "hidden 0" in them, they probably have a custom format on the cell that hides them (so removing the custom formatting should expose them).

But your code should remove them, regardless. Did you try it?
If that did not work, then you you have some other special characters. If you are unsure what they are, locate on your sheet. Let's say it is in cell B11. Then enter this formula somewhere on your sheet and see what it returns:
=CODE(B11)
This will return the ASCII character code for that entry. Then we will at least know what we are working with, and can work from there.

 

[Marcelo Branco]

Another possible reason for hidden zeros

File > Options > Advanced
Under Display options for this workbook
Check if the checkbox beside
Show a zero in cells that have zero value
is checked

M.

 

[wmtsub]

Tks