Clear Blank Cells

[Guest]

In B1 I have =if(A1="this","that","") and continuing down b:b

i then copy b:b and paste special (value) into c:c

I have a sumproduct equation that runs off c:c. the problem is that
apparently the blank cells ("") arent really blank. I get a #VALUE error.
How can I find all the blank (''") cells and clearcontents?

Thanks in advance
jeff

 

[Guest]

What you want to find and clear is "*" , which is wildcard any string.
That would find cells with formulas that equate to zero or "" .

 

[Dave Peterson]

Saved from a previous post:

If you want to see what's left in that cell after you convert ="" to values,
try:
Tools|Options|Transition Tab|Toggle Transition Navigation keys on.

Then select one of those cells and look at the formula bar. You'll see an
apostrophe. (Don't forget to toggle the setting to off.)

When I want to clean up this detritus, I do this:

Select the range (ctrl-a a few times to select all the cells)
Edit|Replace
what: (leave blank)
with: $$$$$
replace all

Immediately followed by:
Edit|Replace
what: $$$$$
with: (leave blank)
replace all

 

[Guest]

Consider:

Sub clear_blank()
For Each r In ActiveSheet.UsedRange
If r.HasFormula And r.Value = "" Then
r.Clear
End If
Next
End Sub