Paste Special as Values: no recognition of blanks

[Guest]

I have a column with formulas. some formulas return blank cells and some
return data.
I copy this column and Paste Special it as Values then I select these values
and press F5 and select Blanks from Special tab options (infact I want to
remove blank cells) but it returns a msg that "No cells were found" despite
having blanks in the range. what's wrong with this.

however when I press delete on any blank cell and then use Go To command to
select blanks it then selects only that cells upon which I pressed Delete.

please explain why Excel do not recognise Pasted as values cells as blanks.

 

[Roger Whitehead]

There is a difference between blank cells, and those that contain an empty
string. I suspect your blank cells are the latter.

Autofilter *might* pick up these empty strings as blanks (then you could
select them all & hit delete) - give it a try?

 

[Guest]

thank you for reply
for the time being I am doing the same but I was curious to know that why Go
To function not recognise blanks because there is nothing in the cell.

 

[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

If you need to do this lots, you can record a macro when you do it manually.